Connecting to Oracle Database Using SQL Server Integration Services

Connecting to Oracle Database using Sql Server Integration Services

I have had to do this on many occasions, unfortunately the Oracle website is not particularly helpful when trying to determine what you need.

You need to download a copy of
"ODTwithODAC1120320_32bit.exe" which can be found at:

http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

The 64-bit version is of no use, the 32 bit drivers will work just fine. Install this on your development machine and on the SQL Server box if you want to run the packages from the agent.

You can then choose the provider ".Net Providers\OracleCLient Data Provider".

The connection string will look some like this (I think 1521 is the default Oracle port):

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xxxxx)))

Problem trying to run SSIS package with Oracle Connection Attunity

The error was being caused on the package trying to run in 32 bit mode. I changed the package settings to 64bit and the SQL Agent settings to run in 64 bits and it started running, it works perfect now :)

Related Topics