SSIS – Oracle Connectivity

Posted on 21/07/2008. Filed under: Software Development |

For the selected view that has not had the pleasure of connecting to Oracle with SSIS from a Windows Server; I will give some tips below.

1. Ensure that you follow the Oracle installation guide to the tee.

2. Make sure that you restart the server once the above installation is complete. (This is required to ensure the registry settings take effect on the Windows Server.)

3. To test the connectivity:

a. Find the SQL installation path(usually $:\Program Files\Microsoft SQL Server\90\DTS\Binn)

b. Run the DTSWizard application

c. At the welcome screen click next

d. At the data source choose OLEDB for Oracle (Note: Do not choose Microsoft OLEDB for Oracle. This is not the native Oracle driver.)

e. Enter you connection properties i.e. Server name, Username, Password etc. (Your oracle DBA should provide this to you.)

f. Then import a couple of rows from a known table. The destination format does not matter since we only want to test the connectivity. I usually pick a text file as my destination and then check the file afterward to ensure that I did get the data from the Oracle server.

If you do not find the OLEDB for Oracle driver when you choose your Data Source with the DTSWizard it means that the Oracle driver installation is faulty of incomplete. The server does not recognize the driver. TIP: Ensure you did restart you server after the Oracle installation.

If you still don’t find the driver and as a last resort you need to un-install the Oracle installation – ensure that you follow the Oracle instructions to the tee.

Note: The above method is the only 64bit connectivity test for Oracle from SSIS. Any other method using BIDS will require that you install the Oracle 32bit drivers on the server since the BIDS run 32bit.

Note: The Oracle 32 bit and 64 bit drivers have the same signature. This means if you are developing on a 32bit environment and your production is 64bit there is no code change necessary to your SSIS packages to run 64bit at run time.

That is the whole chi-bang.



Make a Comment

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: