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

Question

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.

Answer 1

We were getting -

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER The AquireConnection method call to the connection manager failed with error code 0xc0202009

and

SSIS Error Code DTS_E_OLEDBERROR 0x80004005 "[DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error."

We had this problem twice on old jobs, the Provider in the Connection Manager had either possibly changed from "SQL Server Native Client 11.0" to "Microsoft OLE DB Provider for SQL Server" or just become invalid through an update?

On one occasion we were able to change this (to Client 11.0 option) in the package from the dropdown, the second time it happened it was running in the Integration Services Catalogs so we had to edit the Connection String on the Connection Managers (from Configure option) and changed the Provider part of the string from "Provider=SQLOLEDB.1" to "Provider=SQLNCLI11.1" which resolved the issue that time

Answer 2

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