Oracle OLEDB in SSIS - problem with 64bit

I have a 64bit Windows 2003 Server with SQL 2005 (64bit) installed. I would want to fetch data from an Oracle database that resides on a different server and load it into SQL2005 through SSIS. This is part of migration from SQL2000.

I have tried to run Oracle client 10G (64bit) with no success, since SSIS is a 32bit application.

I uninstalled the 64bit Oracle client and installed the 32bit version and can create an Oracle OLEDB Provider as Connection Manager and preview the data from the Oracle source, but when I run the package it displays an error almost directly and the transfer stops.

--

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "BOFLEV.bofinc" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

--

I have tried to change the path for SSIS from Program Files (x86) to ProgramFilesx86 with no luck.

I have changed the Run64BitRunTime to false.

Is there anyone that has managed to resolve this, I really do not want to use a third party software for this!

SSIS has both 32-bit and 64-bit flavors. The dev environment (BIDS) only uses the 32-bit version, though.

Can you connect to Oracle through the 32-bit driver from any other application (like Access)? If not, it sounds lile the driver installation is corrupt.

|||

Yeah we managed to get this one sorted after much searching.

Be very careful with the version of oracle 10g you are using, if memory serves me right it needs to be 10.2..0.1.8 or higher, it is not sufficient to have only 10g 64 bit, it must be patched also.

We used the microsoft oracle driver for the 32bit development of the packages using BIDS, we used configurations for the connections thus allowing us to change the provider type from msdaora to oraoledb.oracle.1 for when we transfered and scheduled the package on the SSIS 64 bit. So far we have used this scenario for about 80 packages without fail.

Regards

John