Microsoft SQL Server to Oracle Database Connectivity from SSIS using Attunity Connector

Comments 0

Share to social media

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)
No alt text provided for this image

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)

No alt text provided for this image

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

No alt text provided for this image

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

No alt text provided for this image

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)

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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

No alt text provided for this image

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

No alt text provided for this image
No alt text provided for this image

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;
No alt text provided for this image
No alt text provided for this image

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

No alt text provided for this image

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.

  1. Open Registry (Regedit in CMD)
  2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node
  3. Create a new key with name of “Oracle”
  4. Go to Oracle and then create a new String Value with name “inst_loc”
  5. Give the value as “C:\Program Files\Oracle\Inventory”
No alt text provided for this image

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

No alt text provided for this image
No alt text provided for this image
No alt text provided for this image
No alt text provided for this image

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.

  • C:\app\client_12c_32\product\12.1.0\client_1\bin;
No alt text provided for this image
No alt text provided for this image

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

Command: — sqlplus

User_Name/Password@(Oracle Server Name)

Step 15 : Set up ODBC-64 Bit for Oracle Connectivity

No alt text provided for this image
No alt text provided for this image

Step 16 : Set up ODBC-32 Bit for Oracle Connectivity same as 64 Bit.

Step 17 : Set up New Connection in SSIS in Connection Managers for Oracle. Select Connection Manager type as “MSORA”

No alt text provided for this image
No alt text provided for this image
 
No alt text provided for this image
 
You are all set ! Enjoy !!!

About the author

Nikhil Thakkar

See Profile

I am a Microsoft Certified Technology Specialist and IEEE Senior Member with 12+ years of Industry experience in the Database Engineering field. Being a Lead Database Administrator I have built and designed mission critical databases for large-scale US based companies in the healthcare, banking and finance, entertainment and gaming industries. I have extensive experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft database Disaster Recovery, High Availability , Performance Tuning , Capacity Planning, Database design and Architecture and Data Modeling. In my free time I enjoy watching and playing sports , Hiking and Travelling.

Nikhil's contributions