Importing Data From Oracle to MS SQL Server Databases Via SSIS

If you’ve been following the blog then you might remember that I have had a couple of failed attempts (first article and second article) at getting connectivity going between an Oracle database as a source and a Microsoft SQL Server database as a destination.

Not to be dissuaded from finding a solution I have found something that I have verified as working at least from a 32-bit perspective. I will try and test this on 64-bit versions of Windows Server 2008 R2 and MS SQL Server 2008 R2 and report back on how things go.

In a nutshell, you need to install the Oracle Data Access Components (or ODAC for short) – you’re most interested in the Oracle Provider for OLE DB. I used an older server running Windows Server 2003 R2 with MS SQL Server 2005 and installed ODAC 11.2 Release 5 (11.2.0.3.20) with Oracle Developer Tools for Visual Studio. There are a stack of other versions there but I just chose that version as it was the most recent one available.

Installation should be fairly straight forward as follows:

  1. Install the ODAC suite,
  2. Update the tnsnames.ora file in the location containing the Oracle Instant Client with the connection details for your desired Oracle databases,
    1. Typically <target>\[username]\product\[version number]\client\network\admin
  3. In your desired SSIS package, create a new database connection and use Oracle Provider for OLE DB,
  4. Specify the server using its SID and enter the corresponding username and password,
  5. Tick Allow saving password,
  6. Click Test Connection to verify everything is correctly,
  7. Click OK if everything checks out.

Now, you may run into a couple of issues along the way. The first one will be an error message complaining about a component not being registered correctly. One such form of the error message can look like the following:

Cannot create and instance of OLE DB provider “OraOLEDB.Oracle”

This is usually caused by one of the DLL files failing to register during initial installation. The way to fix this via an administrative command line is as follows:

regsvr32.exe <target>\[username]\product\[version number]\client\bin\OraOLEDB11.dll

You may need to change the DLL filename if you install an older or newer version of ODAC (so OraOLEDB10.dll for Oracle 10g for example).

The other issue you might encounter is having the password for the Oracle database successfully retained in your SSIS package. This can be rather frustrating as there doesn’t seem to be an apparent reason why this occurs.

The default protection level used by Visual Studio for SSIS packages is EncryptSensitiveWithUserKey however this doesn’t seem to do the trick. Instead, you need to use EncryptSensitiveWithPassword and provide a password for encryption both for saving on the local filesystem and again when exporting to an SSIS repository (if you use that). You’ll also need to provide it again when adding the SSIS package to a schedule to view its options prior to locking it in.

Leave a Reply

Your email address will not be published.