SSIS: Connecting to Oracle using Attunity Connector

Although this is not related to BizTalk, it will be helpful for many as most of the BizTalk developers often work on SSIS too. Recently I worked on a requirement to fetch large amount of data from Oracle and transform and save in SQL Server. SSIS is one good tool for this purpose.

There are two ways of connecting SSIS to Oracle.

1. Using Microsoft Native OLEDB driver
2. Using Attunity Connector

This post will is about using Attunity Connector for fetching data from Oracle. Please note I am using SQL Server 2012 with Windows 2012 Server.

After researching, I found this link which shows how to use Attunity Connector. But this link missed a crucial step in installing Oracle Client which put me in lot of unknown issues. So, below is my version of using Attunity Connector.

Note: I am using a 64 bit OS. Choosing the right version of Connector & Oracle client is crucial for this connector to work.

1. Install Attunity Connector

a. Download 64 bit driver AttunitySSISOraAdaptersSetupX64.msi from here and install it following the wizard.

2. Install Oracle client both 32 bit and 64 bit. 32 bit is for Development and 64 bit after deployment.
(There are a number of posts on why both of these is required. I dont want to touch again)

a. Install 32 bit of Oracle client 11g (Note: I first started with 10g version and ended with an unknown error in SSIS and found that 10g version client has some bug and ended up with installing 11g version of client)

b. Google oracle client 11g 32 bit download or click here. Register and download win32_11gR2_client.zip (around 650 MB)

c. Unzip and Run setup.exe. I had the below error however, I still continued the setup as 10g was not working fine with SSIS.

d. Next step was very important which I took a lot of time to figure it out. Select “Custom” in the next screen instead of “Instant Client”

e. Continue the steps and give some folder path for installation. Example: c:\oracle32\

In the next step, you will get an option to choose which all components to install. Select the below options.
1. Oracle Net
2. Oracle ODBC Driver
3. Oracle Provider for OLEDB
4. Oracle data provider for .NET

f. Once the installation is complete, you will see a folder called
“C:\oracle32\product\11.2.0\client_1\network\admin”

g. Copy the tnsnames.ora file in this path. For those who are new to Oracle world, tnsnames.ora contains the connection string in a particular format. Sample connection string is like below.

h. Make a note of ConnectionName – “SomeConnectionName” in the above image. This is required in later steps

i. Once the installation is done, in registry under Software/Wow6432Node you will see Oracle Folder with “ORACLE HOME” key.

3. Follow the same steps of #2 and install Oracle 64 bit client. You can download it (win64_11gR2_client.zip) from here

Once the installation is complete you will see the the Oracle directory in Windows registry.

4. If you open Path Environment variable, you should see 2 new paths (for 32 bit and 64 bit) added, pointing to bin directory.

This completes the Installation. Sometimes you might need to restart the machine as per MSDN. However it worked for me without restart also. To do the development, in SSIS, you will see a new Oracle Source & Oracle Destination in your Data Flow Toolbox. These Oracle Source & Destination work with Oracle connections. To create a new connection, follow the below steps.

1. Use “New Connection”

2. Select MSORA – This comes with the Attunity Drivers.

3. For TNS Service Name – as per this link , we can give host:port/service_name
But my service name is too long and it is not fitting in the provided text box.
4. So, I used the Connection name given in tnsnames.ora file

5. Now use Oracle Source (Comes after we install the Attunity Driver for SSIS – step 1) to select the tables.

Rest of the development will be as per your need.
Note: you might need to change the protection level appropriately which will allow you to change the connection string / userid, password in the upper environments (QA, UAT etc.) through SQL Job Connection properties. I had some issues with this but I am not expert in this.

Common Errors:

1. When we give wrong TNS Service name in the New connection (host:port/service_name) – it will give an error “OCI error encountered. ORA-12514: TNS:listener does not currently know of service requested in connect descriptor“

This is why we copy tnsnames.ora to that folder C:\oracle32\additionalitems\product\11.2.0\client_1\network\admin and give the connection name of our connection.
2. If we don’t install the Oracle client properly, we will get an error “ORACLE_HOME” not found. In such case we can uninstall and reinstall.

Uninstall Oracle 11g client.

Uninstalling 11g version Oracle client is another painful task. Below steps might be helpful

1. In the same link where we downloaded oracle client, there will be a de install zip file (separate for 32 bit and 64 bit)

2. Unzip this to a folder. You will see a bat file called “deinstall.bat”
3. Go to cmd prompt and give the command deinstall.bat –home “Home Path”
Home Path can be found in the registry

For example, you should give a cmd deinstall.bat –home “c:\oracle32\product\11.2.0\client_1”