Configuring SSIS 2008 to Connect to Oracle

Connecting SSIS to an Oracle database is a lot more difficult than it really should be. There are a couple of reasons for this. The first is because BIDS only runs in 32 bit mode even if you have a 64 bit operating system, but SQLAgent will run as a 64 bit process in a 64 bit operating system. The second is that there is more than one way of getting it to work, so various sites will give seemingly conflicting information, which may be applicable for one case, but not another.

The following are the high level steps I used to get SSIS 2008 to connect to an Oracle database.

1. Install the correct version of the Microsoft Connector for Oracle by Attunity.

Go to the Microsoft Connector for Oracle v1.2 download page if you're using SSIS 2008.
Go to the Microsoft Connector for Oracle v2.0 download page if you're using SSIS 2012

Download the 32 bit version if you're using a 32 bit OS, and the 64 bit version if you're using a 64 bit OS. Note that the 64 bit version includes both the 32 and the 64 bit binaries.

2. Install the Oracle Instant Client. If you're using 64 bit Windows, you'll need to download and install BOTH 32 and 64 bit versions of the client software.

Get the 32 bit Oracle Instant Client here.
Get the 64 bit Oracle Instant Client here.

There were some folks on other sites who couldn't get the Oracle Instant Client to work with SSIS, and therefore advocated installing the full version of the Oracle database software instead. The downside with this of course is that you'll end up eating a lot more disk space and memory than what is necessary, not to mention the wasted software licenses. The above pages also contain links for downloading Sql*Plus, which I like to use for testing purposes.

The Oracle Instant Client packages do not come with their own install programs. You'll have to manually unzip them to directories of your choosing. If you're installing both versions, you should unzip them to different directories. I like adding x32 and x64 to the directory names so I can easily differentiate between them. Note that as of version 11.2, all these zip files unzip to the same subdirectories by default, so I would unzip them in the following order.

• Unzip Oracle Instant Client 32 bit
• Unzip Sql*Plus 32 bit
• Rename subdirectory by inserting _x32 to the directory name. In my case, the path is x:\oracle\instantclient_x32_11_2.
• Unzip Oracle Instant Client 64 bit
• Unzip Sql*Plus 64 bit
• Rename subdirectory by inserting _x64 to the directory name. In my case, the path is x:\oracle\instantclient_x64_11_2.

You also need to create your own tnsnames.ora file. Set the TNS_ADMIN environment variable to point to the directory where you the put tnsnames.ora, and add the Instant Client directory to the Path variable. I use the 64 bit version in the Path in my case, but the 32 bit version should work, too. It should be noted that if you're not familiar with tnsnames.ora, manually creating that file yourself is not trivial. It would be best if you can work with an Oracle DBA to create the file for you.

3. Configure the Microsoft Connector and the Oracle Instant Client.

Follow the instructions here for installing and configuring the Oracle Connector. It shows the registry entries needed to allow the Microsoft Connector to recognize the Instant Client, and how to add the Oracle Source / Destination to the BIDS Data Flow toolbox. If you have carefully followed the instructions here and in the linked pages, you should now be able to add your Oracle data flows into your package.