CODEWITHSUNDEEP
sql-serveroracle-databasessis
chandu
chandu

Reputation: 1

SSIS package with oracle connection failing when run via SQL job

I have developed an SSIS package and it is running fine from visual studio. I have deployed this package to SQL server and created a job for its execution. Now the job is failing throwing an error at Data flow task which contains Oracle source.

Please find the below error that I received:

Error: The requested OLE DB provider OraOLEDB.Oracle.1 is not registered. If the 64 bit driver is not installed run the package in 32 bit mode.Error code: 0x00000000 An OLE DB record is available. Source: "Microsoft OLE DB Servoce Components" Hresult: 0x80040154. Description: "Class not registered".

Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle_Source_DWH" failed with error code 0xC0209303. there may be error messages posted before this with more information on why the AcquireConnection method call failed.

Please help me how to resolve this error.

I tried uninstalling the Oracle client but didn't work.

Thanks in Advance, Chandu

Upvotes: 0

Views: 2432

Answers (1)

Pavel Botygin
Pavel Botygin

Reputation: 187

I suppose that you have Oracle client installed on your SQL Server machine, not only on your developer machine. In that case the most frequent reason of packages executing successfully in VS and fails on SQL Server is that VS using x86 provider, but SQL Server is using x64 version. You have 2 ways to fix that issue:

  1. Install x64 (or both x86 and x64 if you develop on server machine) Oracle clients on SQL Server machine.
  2. In theory (I've never used this) - on the "Execution options" tab of the job step set checkbox "Use 32 bit runtime".

Upvotes: 0

Related Questions