Posted: 1/19/2012
I have created a SSIS package that is accessing an Oracle server with a user name and password. I saved the package from Visual Studio to my SSIS Store utilizing the "Server Storage" option to save the user name and password. I can run the package via Visual Studio and my SSIS Integrated Services Store and it is successful. But when I add the package to the SQL Job Agent, the package fails with the following error below. This is my first package that I have created that is connecting to an Oracle Server.
ERROR CODE:
Source: LoadSurgerySIS Connection manager "sisi.sisirpt" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2012-01-18 19:00:35.46 Code: 0xC020801C Source: Load UOSSISLoad SIS Pull Volumes by Specialty and Date [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "sisi.sisirpt" failed with error code 0xC0202009.
Recap
I appreciate any help that can be offered! Thanks! -Jeff
Posted: 2/6/2012
Jeff-
https://forums.oracle.com/forums/thread.jspa?threadID=1008412
This thread suggests that instead of using the Microsoft OLD DB Provider for Oracle, you should use the "Oracle Provider for OLE DB".
Hope it helps,
Keith Hyer