SSIS Configuration on SQL Server

0
votes

I have scenario where I have several variables eg. sourceFilePath, sourceFileName and etc. I am storing the variables as a configurations in SQL Server Table. To store this configuration, we have to provide a connection (I have opted for OLEDB Connection).

When I am executing this from BIDS, it is working very fine and there are no issues at all. I can change the sourceFilePath and sourceFileName from the Configuration table and the package is taking the changed path as well.

But the problem arrived when I have deployed the Package on SQL Server. I am not able to get the configuration values from table and it keeps on saying "Login failed from user.... Unable to load the configuration ...... ".

What can I do to avoide this situation?

Yes, I tried giving the password explicitly when scheduling the job but I can do the same for production server as it stores the password as a plain text.

Any help is highly appreciated.

Many thanks.

1
ssis
The normal approach here is to create a parameter to the package / project for the password and supply the necessary values at runtime. If you are using the 2012 version you can even create an environment to store typical or often-used parameter values. The parameter values are stored encrypted in the SSIS catalog is you mark them as sensitive. – Brian O''Byrne
Hi Brian, Thanks for the comments. I have passing the values from configuration tables itself but at run time also, before getting the values from tables it seeks the connection which is not happening. – Raj Jayaswal

1 Answers

1
votes

Thanks to all. I just need to set ProtectionLevel property for my packages and the rest is done. Such a small thing and it was making me nuts...