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:

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).