The AcquireConnection method call to the connection manager failed with error code 0xC0202009

3
votes

Error message:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "REMEDY" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.[SSIS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

  • I'm trying to import data from ORACLE DB to SQL SERVER DB but receive the above error at package "execution" phase
  • OLEDB connection manager to ORACLE is successful when tested.
  • In DFT No error or warning after columns mapped
  • IN OLEDB source, no error or warning. Able to parse the SQL query and preview the columns to build queries.
  • Oracle client 32bit. ORACLE HOME path is set to D:\app\product\instantclient_11_2
  • Have installed 64 bit, instant client, also and configured the ORACLE_HOME also.

I tried the below troubleshooting,

  1. changed the transactionOption to NotSupported ( For control flow and DFT properties both)
  2. Have set the delayValidation to TRUE ( DFT properties )
  3. Have set the => Project -> Properties -> Debugging -> Run64BitRunTime = False
  4. Tried executing the package via DTEXEC utility
  5. Package protection level is EncryptSensitiveWithPassword
  6. Have set the 32bit runtime execution at SSIS JOB

But still, receive the same error. Requesting assistance.

1
sqloraclessisclientoledb
To restate: you have an SSIS package that works with Oracle data. It works fine on your machine but when you run it on the server, it raises the failure to connect error? – billinkc
This whole setup is directly on server only and just "Test connection", query parse in DFT is sucessful . But DFT executing results in error. – Kavin Israel
You referenced SSIS Job so I'm a touch confused. Does the SSIS package work from Visual Studio? Is it only the job that is failing to work? – billinkc
No the ssis package doesn't work at visual studio itself. After googling I read the job will run as ssis job with 32bit runtime checked....even though it doesn't run on ssis. Tats y I tried creating a job. – Kavin Israel
Also, if you have SQL Enterprise Edition, the Attunity connectors available through Microsoft tend to have the fewest issues and the best performance. – digital.aaron

1 Answers

0
votes

I have upgraded to SSDT 2012 and utilized Oracle Attunity driver to resolve this issue. Thanks digital.aaron & billinkc for your intreset on my question