I recently ran into an issue with developing SSIS packages on a development server running SQL Server 2008 64 bit (X64). After intalling the 64 bit 10G Instant Client and Data Access Components (DAC), I was able to create a linked server and connect to as one would expect to an Oracle server.
The fun started when trying to develop against the same Oracle Servers in SQL Server Integration Services 2008 (SSIS). After recieving a slue of Driver and TNS Errors I found some compelling information. As it turned out (and I should have realized) the Business Intelligence Development Studio (i.e. BIDS, Visual Studio 2008) is a 32 bit application. It runs under the “Program Files (x86)” directory.
In my adventure to get through this I installed the 32 bit Oracle Instant Client and DAC side by side with the 64 bit varient. But the fun doesn’t stop here. As it turns our Oracle for one reason or another seems not to like the “Program Files (x86)” directory. Specifically it seems not to like the ‘(‘ and ‘)’ characters.
To get around this you need to run any programs from the directory using the old school methods of Progra~2. In other words to run and develop in BIDS from the following command: C:\Progra~2\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
As it turns out SSIS when installed on a 64bit Server has several components that include:
Visual Studio (BIDS) is a 32bit application.
SQL Server Management Studio is a 32bit application.
dtexecui.exe is a 32bit application.
dtexec.exe has both 32bit and 64bit versions.
SQLAgent is a 64bit process.
When running a package from BIDS the process will use the 32 bit edition and the x86 Oracle drivers. the same holds true when running the package interactively from Managment Studio (SSMS). However, when scheduled the package will run in 64 bit and utilize the 64 bit drivers.
Now with this one could assume that you could call the command direct running the 32 bit dtexec.exe for other 32 bit drivers and I plan to test in the very near future with a legacy driver (Rumba/AS400).
I hope this helps some one. A big thanks goes out to Greg Galloway and Steve McHugh for the help and information around this issue.