I am modifying an SSIS package in which some of the Data Flow Tasks have Lookup Transformation Editors. The modifications I am making entitle the passing of an additional parameter to some stored procedures, and this parameter comes from a variable in the package.
I have set the cache mode to either partial cache or no cache so that I can add a parameter on the advanced page, and have used the Derived Column Transformation Editor to add a derived column using the variable's expression so that it is listed with the other Available Input Columns in the Lookup Transformation Editor, but when I go to add parameters to my SQL statement, the only options available are the available input columns that are linked to corresponding columns in the Available Lookup Columns.
There is not, however, any corresponding column in the Available Lookup Columns to attach to the variable I need to use. How might I add this parameter to my SQL statement?
For more information on the problem, the stored procedures are being modified to handle tables that have recently been column-encrypted by symmetric key by password, and the plan is to pass the password via parameter. To reduce duplicity and for security concerns we are trying to keep the password as isolated as possible and only access it from specific locations.
As for the lookup, this task deals with merging data from a staging database. It is using the lookup to find matching records to update in the primary database.
Thanks for your help.
Perhaps it is not the most proper solution, but I have figured out a way to make it work. I have added the password to the result set that is returned and can link the derived column to the available lookup column that way, and then use it as a parameter. It does seem a bit circular and not an ideal solution, but the stored proc will only ever be used in this package.