SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component

In trying to use SSIS for some ETL, I wanted to be able to pass variables about in my packages to use in updating the status of tasks in a SQL Server database.

I got my package to set a variable to the result passed OUT of a stored procedure, but I could not get it to work when trying to pass variables IN to a stored procedure. I kept getting errors such as "The query failed to parse. Syntax error, permission violation, or other nonspecific error".

I spent hours trying to get it to work.

It would not work.

It drove me nuts.

Then I found this post on Rafael Salas' blog which, ten minutes after reading, led me to the answer to my problem and a working DTSX package.

Basically all that's involved is creating another package variable to contain the SQL you want to use, then evaluating it as an expression which allows you to use your other variables in the SQL and to execute the stored procedure as desired.

There's nothing quite like seeing those DTSX package tasks lighting up green as it runs successfully!