SSIS Connectivity to Oracle

Question

Hi Friends,

I sawa lot of articles for this question but didnot get any relevent answer that could solve my issue.

I have a 32 bit SQL Server. On this Server when I create a connection to Oracle using Connection manager, I am able to test the connection successfullt

Now I have this 64 bit SQL server, On this Server when I create a connection to Oracle using Connection manager, I get the below error message:

when using : - Microsoft OLEDB Provider For Oracle Provider

Test connection failed because of an error in initializing provider.
Attempt to load Oracle client libraries threw BadImageFormatException. 
This problem will occur when running in 64 bit mode with the 32 bit Oracle client
components installed.

when using : - Microsoft .Net Provider For Oracle Provider

Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.      Provider is unable to function until these components are installed.

I have installed Oracle client 11g, also have the entry in TNSnames.ora

Answer 1

If you are specific about BIDS (Business Intelligence Development Studio) or SSDT (SQL Server Data Tools) then it will run on 32 bit drivers but when running a package it emulates a 64-bit environment by default.. However, you can change the debugging mode to 64 bit or 32 bit anytime you want by going to

Project Properties > Configuration Properties > Debugging > Run64bitRunTime

so  you need to create a 32 bit DSN using the odbc administrator application in the Windows\SysWOW64 folder.

Hope this will help

Answer 2

On your 64-bit server, what version of Oracle have you installed?  32 or 64 bit?

You should bear in mind, to develop using BIDS you need the 32-bit version.  But, if were to develop your package on your 32-bit box, then run it on your 64-bit box ( ie where only 64-bit Oracle is installed ), then that should work.