My SSIS package includes a T-SQL task. I have a package parameter that I want to pass into my T-SQL task but I can't find the correct syntax to do this:
DECLARE @myVariable int;
SET @myVariable = $Package::myParameter --not working
SET @myVariable = @[$Package::myParameter] -- also not working
What is the correct way to pass parameters to a T-SQL task?
I'd recommend using an Execute SQL Task as it provides more functionality than an Execute T-SQL Statement Task. However if you're looking to use the T-SQL Task with a parameter this can be done by creating a string variable with an expression that includes the parameter. An example of this is below. To set this as the statement for the T-SQL task, go to the Properties window of the task (press F4), click the ellipsis next to the Expressions field, select the SqlStatementSource property and add the string variable containing the T-SQL as the Expression. Since the variable in your SQL is of the INT data type, I'm assuming the package parameter also is, thus it needs to be cast to a string to be included as part of the expression in the string variable. This will still be parsed as a numeric data type and submitted to SQL Server as such. This casting is done with the (DT_STR, length, code page) function below. This just uses an example length of 10. As a side note, the (DT_WSTR, length) function would be used for Unicode data. Make sure to enclose the SQL text in quotes as done below. Also be aware that parameter names are case sensitive within an expression, for example @[$Package::MyParameter] would return an error if the parameter name was @[$Package::myParameter], starting with a lower case m.
"DECLARE @myVariable INT;
SET @myVariable = " + (DT_STR, 10, 1252)@[$Package::myParameter] + "
UPDATE Database.Schema.Table
SET NAME = 'TW'
WHERE ID = @myVariable"
You can't pass parameters to a T-SQL task.
According to the documentation:
If you need to run parameterized queries, save the query results to variables, or use property expressions, you should use the Execute SQL task instead of the Execute T-SQL Statement task. For more information, see Execute SQL Task.
