SQL Server 2012 connectivity to Oracle

Question

I am trying to copy data from Oracle to SQL Server 2012, and I get the following message when selecting Data Source as Microsoft OLE DB Provider for Oracle:

Test connection failed because of an error in initializing provider. Oracle client and network 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.

I tried using .NET Framework Data Provider for Oracle and I get:

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 (system.data.oracleclient).

In SQL Server 2000 (which I am trying to move to SQL Server 2012/2014), I have the option of selection Oracle in OraClienthome directly.

Some additional information that may help diagnose the problem:

Using Toad 64 bit, it points to the 64 bit Oracle download; however, I can not tell if it is using a 32 bit driver or 64 bit driver. I can also run queries, etc. without issue.
ODBC, I can see Oracle when making a 32 (I am guessing) bit ODBC connection named Oracle in OraClienthome, but not in SysWOW64 odbc connection.
I successfully established a linked server connection on a server running 64 Bit SQL Server 2012. OraOLEDB.oracle shows up under Server Objects  Linked_Server  Providers
While creating an SSIS package, I am unable to establish a connection to Oracle.
I can successfully run queries in MS Access and Excel.
(NEW) I can copy files using Import Export Data 64 bit, but not 32 Bit.

Any help would be greatly appreciated!

https://clinthuijbers.wordpress.com/2012/06/24/ssis-and-an-oracle-ole-db-source/ — Lukasz Szozda, Aug 24 '15 at 18:59

The Gambill · Answer 1 · 2015-08-26T03:01:37.413

0

In many cases the 64 bit drivers are not compatible and you have to install the 32 bit drivers then be sure you are selecting to use the 32 bit drivers if you create a job to run the package which is in the command options as the last check box. I also use toad to pull data from oracle on my desktop and it works fine with the 64 bit driver but on our new server I had to install the 32 bit drivers even though I was able to create odbc connection with connection manager. Also had to reboot win server after instal before I could get it to take.

edited Aug 26 '15 at 03:01

answered Aug 26 '15 at 02:54

The Gambill

181
1
12

I do have both 64 bit and 32 bit drivers installed. I see the 32 bit driver in the ODBC connectors, and the server sees the 64 bit driver. – Harold Hechinger Aug 26 '15 at 14:09
Did you try to run the job forcing the use of the 32bit runtime on the execution tab of the step? – The Gambill Aug 26 '15 at 15:11
That fail since SSIS is not seeing the 32 bit drivers. – Harold Hechinger Aug 26 '15 at 18:45

score -1 · Answer 2 · answered Aug 25 '15 at 08:32

-1

Try to install and use Oracle Client on the MS site, and transfer the data using SSIS. I got same problems, but when i've used connection using oracle client (you will see it in connection selection options) all worked ok.

Good luck!

answered Aug 25 '15 at 08:32

nevsv

2,448
1
14
21

SQL Server 2012 connectivity to Oracle

2 Answers2

Linked