SSIS - [OLE DB Source [392]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager

If you are a ETL developer with SSIS you will be definitely faced this issue at least once. I have spent hours of my development efforts to sort out this issue so I hope this post will help developers in future.

I was pulling the data from oracle database and pushing the information into MS-Excel report after cleansing the data. I had used ADO.net provider for oracle and given the required information such as user name, password, server name etc. Tested the connection and it was perfect. But when I executed the whole package together it started throwing this error.

As every developer search for this issue in internet, I also spent hours to get the answer for my issue but most of the answers says problem fixed by doing below changes to the SSIS packages.
   - Change the Delay validation property of the task true so that it will not validate the connection
      immediately.
   - Change protection level of the package to DontSaveSensitive
   - Change Run64BitRunTime property under Configuration -  Debugging to false

All these changes did not give me the desired result.

Finally I had created package configuration and selected only connection string leaving all other fields blank for the file.






rebuild the package once again and run it. amazing it is working fine !!!!!


Even though ADO.net oracle provider connection saved the credentials, it was not able to retrieve at the time of execution. when you create the package configuration it will pick the credentials from the configuration file and execute the package.














Comments

Popular posts from this blog

Hadoop - Hive - Load data from csv/xls files

Microsoft BI Implementation - Cube back up and restore using XMLA command