SSIS to Oracle Connection

 

Do the following steps to connect to Oracle in SQL Server Integration Services:

 

1.      Install ODTwithODAC1120320_32bit to see Oracle provider for OLEDB in Visual Studio

 

a.      Download URL : http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html (you need also to download and install ODAC 64-bit)

 

b.      This software will install Oracle client, ODAC drivers and Oracle provider for OLEDB driver in Visual Studio

 

c.       Remember the installation path, it will be used in the next steps

 

2.      Now you need to configure connections to connect to oracle

a.      Go to the installation path you have chosen while installing in step 1c

 

b.      Point to C:\~\product\11.2.0\client_1\network\admin\sample and open tnsNames.ora

 

c.       Copy tnsNames.ora to C:\~\product\11.2.0\client_1\network\admin

 

d.      Copy a sample connection string and replace with your oracle connection strings e.g. : <Oracle_Server_Name_Custom>= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <Oracle_Server_Name>)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = <Oracle_Service_Name>) ) ) e. <Oracle_Server_Name_Custom> can be anything but its good practice to use <Oracle_Service_Name>

 

3.      Log off the computer and Login again (otherwise you will not see username and password option while using oracle provider for OLEDB in SSIS)

 

4.      Open any of the SSIS package, in connection manager > Right click > New OLEDB connection > Choose Oracle provider for OLEDB > Enter <Oracle_Server_Name_Custom> used in tnsNames.ora in point 3d, user name and password of the oracle > Test the connection

 

You need 32bit for SSDT and 64bit for executing the package as a job since SQL Server is 64bit.

 

Read my blog on using ODBC 32bit and 64bit drivers scenario.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s