DevOps, CI/CD and Automation

Hi.

I have an SSIS (SQL Server Integration Services) Package that reads a view on Oracle and loads data into table in SQL Server 2005. This is on a Windows 2008 Server (64-bit). SQL Server 2005 with SP3. Oracle Client 11g (32 bit & 64 bit) Runtime Version installed on server. The server has been rebooted since installing OracleClient.

I get the following error when run as a SQLAgent job 64 Bit (Execution type SQL Server Integration Services Package)...

Started: 9:29:24 AM
Error: 2010-01-04 09:29:25.37
Code: 0xC0202009
Source: SADM_CURR_Address_and_Cell_Phone-Oracle Connection manager "Oracle/PeopleSoft"
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: 2010-01-04 09:29:25.37
Code: 0xC020801C
Source: DFT-Oracle to SS2005 OLE DB Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle/PeopleSoft" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error
Error: 2010-01-04 09:29:25.37
Code: 0xC0047017
Source: DFT-Oracle to SS2005 DTS.Pipeline
Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2010-01-04 09:29:25.37
Code: 0xC004700C
Source: DFT-Oracle to SS2005 DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2010-01-04 09:29:25.37
Code: 0xC0024107
Source: DFT-Oracle to SS2005
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 9:29:24 AM
Finished: 9:29:25 AM
Elapsed: 1.108 seconds


When I execute this same exact package as a SQLAgent job 32-bit (type Operating System CmdExec) it runs successfully. When I run the package using the Execute Package Utility, it runs successfully. When I am editting the package in BIDS I can connect to Oracle. For both 64 and 32 bit, I use a dtsconfig file which specifies the Oracle connection string and password.

Someone mentioned on another forum that maybe there is no Oracle ODBC driver for 64-bit Windows. I would assume that the OracleClient 64-bit would include that.

I hope I have included all info needed. Please let me know if you may have a resolution to this problem.

Thanks.

John