SSIS,SSRS,SQL Server Information: Microsoft SQL Server to Oracle Database Connectivity from SSIS using Attunity Connector

Almost every environment now a days need to source data from different source system. Here is how you can establish connection using Attunity Connector from Microsoft SQL Server to Oracle Database from SSIS.

This method is extremely fast if you want to land millions and millions of records on the fly from Oracle to SQL Server.

Here are the steps.

Step 1 : Download Microsoft Connectors v2.0 for Oracle using the link below.

https://www.microsoft.com/en-us/download/confirmation.aspx?id=55179

Download AttunitySSISOraAdaptersSetup.msi (32 Bit)
Download AttunitySSISOraAdaptersSetupX64.msi(64 Bit)

Step 2 : Run Setup for both 32 Bit & 64 Bit Attunity Connector on the SQL Server. You can verify this install in the control panel in Program and Features. (*Always run Setup as an Administrator)

Step 3 : Once Installation is done for both 32 Bit & 64 Bit Attunity Connector on the SQL Server you will be able to see a new component in SSIS Data Flow called “Oracle Source” & “Oracle Destination”.

Step 4 : Download Oracle 12c — 64-bit Oracle Data Access Components (ODAC) Downloads using the link below. http://www.oracle.com/us/products/tools/index-090165.html

64-bit ODAC 12.2c Release 1 (12.2.0.1.0) for Windows x64 [Released June 1, 2017] (ODAC122010_x64.zip — 415 MB (435,541,788 bytes))

Step 5 : Download Oracle 12c — 32-bit Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit) using the link below. http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html

Oracle Database Client (12.1.0.2.0) for Microsoft Windows (32-bit) (winnt_12102_client32.zip (32-bit) (902,922,170 bytes) )

Step 6 : First Run Setup for 64 Bit Oracle. Below folders will be created in C Drive. (Recommended to create in C Drive) (*Always run Setup as an Administrator)

Incase if you see this error below during installation, ignore the error and continue with the installation.

Step 7 : Modify this “tnsnames.ORA” file with appropriate Oracle TNSServicename.

Step 8 : Edit the “System Environment Variables”. Go to the control Pane and top search box search for environment click on Edit the System Environment Variables, click on Environment Variables go to path in System Variables. Make sure below Environment Variables are available if not copy in the Path folder.

C:\app\client\product\12.1.0\client_1\bin;

Step 9 : Test the Connectivity to Oracle after 64 bit Install by running SQLPLUS on CMD.

Command: —

sqlplus

User_Name/Password@(Oracle Server Name)

Step 10 : If you start running setup for 32 Bit Oracle Client without below steps, setup will kick you out. Follow below steps and restart the installation setup.

Open Registry (Regedit in CMD)
Go to HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node
Create a new key with name of “Oracle”
Go to Oracle and then create a new String Value with name “inst_loc”
Give the value as “C:\Program Files\Oracle\Inventory”

Step 11 : Run Setup for 32 Bit Oracle Client. Make sure to run “Administrator”. (*Always run Setup as an Administrator)

Step 12 : Copy “tnsnames.ORA” & “sqlnet.ORA” file from 64 bit folder “C:\app\client\product\12.1.0\client_1\Network\Admin” if you don’t see after 32 bit Oracle Client Install.

From:

C:\app\client\thakkn91\product\12.2.0\client_1\Network\Admin

To:

C:\app\client\thakkn91\product\12.1.0\client_1\network\admin

Step 13 : Edit the “System Environment Variables”. Go to the control Pane and top search box search for environment click on Edit the System Environment Variables, click on Environment Variables go to path in System Variables. Make sure below Environment Variables are available if not copied in the Path folder.