SSIS: Accessing value of sensitive parameter for lookup

Question

I have a package that is to use sensitive environment variables. These variables are passwords for symmetric keys to encrypt and decrypt data from the databases, and dynamic SQL must be used, so I must pass the passwords as parameters to the SQL statements in the components. In lookups, the only way to do that is if they are input columns, but I cannot derive columns from the values of sensitive parameters with the error, "Accessing value of the parameter variable for the sensitive parameter "Password" is not allowed. Verify that the variable is used properly and that it protects the sensitive information."

How do I actually use the sensitive parameters within the package itself?

Answer 1

you can retrieve sensitive environment variables using script task.

http://www.mattmasson.com/2012/01/retrieving-the-value-of-a-sensitive-parameter-in-a-script/

code sample

string value = Dts.Variables["$Package::tabcmd_password"].GetSensitiveValue().ToString();

here tabcmd_password is the variable name where sensitive info is stored.

Answer 2

This would cause the password to be at risk and would not be a good idea. Set the connection using four expressions. Server, initial catalog, user name, password.