Connecting to Oracle with SSIS 2008

This isn’t my first adventure in connecting to Oracle from SQL Server. No, in fact, I wrote a guide on that very topic nearly 3 years ago. Unfortunately—as a technologist, but fortunately as a blogger—the times change as does the method for setting up the configuration. On the plus side, I’m much more confident in the setup and am convinced that it’s easier than ever to connect SSIS to Oracle. Without further adieu, let’s get started by downloading and installing a couple of necessary components:

• Attunity SSIS Oracle Connector v1.1
• • After installation, you’ll need to enable the menu items in the Data Flow Source & Destination
  • • Create Data Flow Task
    • Switch to Data Flow
    • Pull the Toolbox out
    • Right-click→Choose items…
    • Activate the SSIS Data Flow Items tab
    • Enable “Oracle Destination” and “Oracle Source”
• Oracle Instant Client Basic package
• • Download and extract both 64-bit and 32-bit versions if you’re using a 64-bit OS
  • • Only need the 32-bit version if installing on 32-bit OS
  • Installation location is up to you, but you’ll need to note it later. This tutorial assumes C:\oracle\ as the base location.
  • • For example, install the 64-bit package at “C:\oracle\instantclient_x64_11_2″ and the 32-bit package at “C:\oracle\instantclient_x86_11_2″

Once you have all the items installed, you’ll need to add a couple of registry keys with regedit so that the SSIS connector can find the Oracle Instant Client. Create the necessary keys and string values below where they don’t exist, updating files paths to point to where you extracted the items above (again, C:\oracle\ is assumed for this tutorial):

•  [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
• • New→String Value→Name = TNS_ADMIN→Value = C:\oracle\tnsnames.ora
  • New→String Value→Name = ORACLE_HOME→Value
  • • For 64-bit OS: C:\oracle\instantclient_x64_11_2
    • For 32-bit OS: C:\oracle\instantclient_x32_11_2
• [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE] (64-bit OS only)
• • New→String Value→Name = TNS_ADMIN→Value = C:\oracle\tnsnames.ora
  • New→String Value→Name = ORACLE_HOME→Value = C:\oracle\instantclient_x86_11_2

Windows Registry Editor Version 5.00
 
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE]
"TNS_ADMIN"="C:\\oracle\\tnsnames.ora"
"ORACLE_HOME"="C:\\oracle\\instantclient_x32_11_2"
 
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
"TNS_ADMIN"="C:\\oracle\\tnsnames.ora"
"ORACLE_HOME"="C:\\oracle\\instantclient_x64_11_2"

Windows Registry Editor Version 5.00
 
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]
"TNS_ADMIN"="C:\\oracle\\tnsnames.ora"
"ORACLE_HOME"="C:\\oracle\\instantclient_x32_11_2"

This setup allows you the choice of either using the names defined in TNSNAMES.ORA at the location specified in the registry above OR specifying the server inline in the format of <server.name.trb:port/service>. For example: <servername.path.to.tld:1521/ORA10DEV> (without the angle brackets). That’s really it! The process is much more streamlined than it was just a short while ago. Two installations and a couple regedit tweaks and you should be all set!