SSIS - Attunity Connector for Oracle

Peter Schmitz

Staff member
If you ever needed to pull data from Oracle using SSIS, you'll know it's quite a pain, and the process is rather slow.

Your first struggle will be to figure out exactly which Oracle client you'll need. Chances are you run a 64-bit server. The problem here is that SSIS runs in Visual Studio, which is a 32 bit application. You probably need to install both 64 and 32 bit (the package can actually be ran in 64-bit mode; you just cannot develop it unless you have the 32 bit connector running).

Once that is done, you can use an ODBC or OLE connector to connect to Oracle. Ole DB is faster, but still slow. Luckily Attunity provides a blazing fast component. However, you might end up running into all kinds of issues, requiring you to set up registry keys in order for SSIS to pick up the proper ORACLE_HOME path, and even when that is done, you might encounter the following error:

OCI error encountered. Error encountered while creating OCI environment

As it turns out, this was caused by me installing only the "InstantClient" installation of the Oracle Client Installer. Switching to the full (Administrator) version fixed the issue and allowed me to connect successfully.