SSIS Package Reading from Oracle Has Error (64 bit)

Last Post 07 Jan 2010 08:14 AM by johnbarry. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
johnbarry
New Member
New Member

--
04 Jan 2010 07:36 AM
Hi.

I have an SSIS Package that reads a view on Oracle and loads data into table in SQL Server 2005. This is a Windows 2008 Server (64-bit). SQL Server 2005 with SP3. Oracle Client 11 (32 bit & 64 bit) 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.

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

Thanks.

John

J013-B
New Member
New Member

--
04 Jan 2010 03:40 PM
I remember seeing a similar error with an SSIS package that was reading from an Excel source on a 64-bit agent job. The problem ended up being that there was no 64-bit driver available for Excel, so the package had to be either run in a cmdexec step or using dtsutil. I would not be surprised if the same is true for Oracle as well.
johnbarry
New Member
New Member

--
05 Jan 2010 04:44 AM
I have installed the Oracle Client 11.1.0.7.0 64-bit Runtime version. Wouldn't drivers be part of the install? I'm going to try to determine that. That would be a problem if it is trying to use a 32-bit driver.
johnbarry
New Member
New Member

--
05 Jan 2010 11:13 AM
At this point I am pretty convinced that, even though the server is 64-bit and SSIS is 64-bit, something about the way SSIS accesses Oracle...it ends up trying to do it at the 32-bit level.
johnbarry
New Member
New Member

--
07 Jan 2010 08:14 AM
Was able to get it to work! Had been using the 'Microsoft OLE DB Provider for Oracle' for the source connection. Created a new connection using 'Oracle Provider for OLE DB' and wha-la. It worked.


Acceptable Use Policy
---