Wednesday, August 4, 2010

SSIS and an Oracle OLE DB Connection

Here is an interesting situation I came across working with SSIS and Oracle.

I have a server with operating system Windows 2008 Server Standard Edition and SQL Server 2005(x64 bit) installed on it. I accidently installed the Oracle 10.2 client twice. I promptly removed the second Oracle client and verified I could still connect to Oracle databases in SQLPlus.

I have a SQL Server Integration Services (SSIS) solution that extracts from SQL Server and sends to an Oracle database. After my client install misadventure, when checking the Oracle Provider for OLE DB connection, I noticed something was wrong.

When trying to test connection to get an error, I got:
"oraoledb.oracle.1 provider is not registered"

Yikes, this was working before. How can I fix this and save the 13 packages that will run tonight? Surely, they will all error!

When trying to run the package anyway, I would get this error:
Code: 0xC020801C Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle Provider for OLE DB" failed with error code 0xC0202009

After looking through a myriad of other posts, it appears that the Oracle OraOLEDB10.dll file became unregistered at some point.

The fix:
START/Run then,
regsvr32 C:\oracle\product\10.2.0\client_1\BIN\OraOLEDB10.dll

Yes! The connection worked in the packages and the jobs all ran to success!

No comments:

Post a Comment