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.
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.
