I'm in the process of migrating an old SQL Server 2000 SP4 on Windows 2000 to a new SQL Server 2008 SP2 on Windows 2008. In the old server we have a DTS that does some simple tasks against an Oracle Server (3 transformations - Copy Columns from 3 SQL tables to 3 Oracle tables, nothing fancy).
Thus, we have the need of using SSIS in our SQL Server 2008.
However, we have not been able to make it work. In our test machine we did the following:
- Clean Install of Windows 2008 R2
- Install SQL Server 2008 x64 -- With Client & Admin tools, SSIS, BIDS, and so.
- Install Service Pack 2.
- Install Oracle Client.
- Install AttunitySSISOraAdaptersSetupx64.msi from MS.
I have tried installing all the possible Oracle clients available: 10g R2 (10.2.0.4) x32, x64, and 11g R2 (11.2.0) x32 and x64. The tnsping works, I can connect with SQLPlus*, and even can create ODBC with Oracle driver to the desired Oracle database, no problem.
However, when I try to define a new Datasource in SSIS, I'm unable to make it work. Errors range from the "wrong architecture" (When only x64 Oracle client is present -- Have tested having BOTH clients, x32 and x64, not working either) to even more insane things (TNS-xxxx Oracle errors which have no sense, as I've tested oracle connectivity and it works).
I think the main problem resides in the fact that I have a w2k8 (x64) OS, with SQL Server 2008 (x64) and that the tools (SQL Server Management Studio AND BIDS) are x32, thus the architecture error. But that only happens when setting up an Oracle connection, and have tested with both x32 and x64 clients, and can't make it work.
Any ideas ?