SSIS - SqlStatementSource expression vs Variable Source Type
Question:
I use SSIS in VS 2017 and SQL Server 2016.
I would like to know what is the difference between the following methods to for setting
SQLStatement's value in Execute SQL Task:
First Way
- Declare a variable
- Configure Execute SQL Task like below
Second Way
- Declare a variable
- Configure Execute SQL Task like below
In both way, I use variable to assign
SQLStatement's value, but in the first one, I set it directly, and in the second one, I use expressions.
Answer:
There is no better approach, each one has its own use case
- The first approach can be used if the developer decided to store the whole SQL Command inside a variable.
- The second can be used if the SQL Command is built based on other variables, such as passing the table name dynamically, or passing parameter because Execute SQL Task doesn't support parameterized queries:
"SELECT * FROM [" + @[User::SchemaName] + "].[" + @[User::TableName] + "]"
As i remember, in the old SSIS versions, the first option was not available. Then it is added later (I think in 2012) version to facilitate the developer work in case the the whole SQL command is stored inside a variable rather than adding a one variable expression:
@[User::strQuery]Official documentation
Variable option (1st approach)
Set the source to a variable that defines the Transact-SQL statement. Selecting this value displays the dynamic option, SourceVariable.
Expression use case (2nd approach)
When you use an OLE DB connection manager, you cannot use parameterized subqueries because the Execute SQL Task cannot derive parameter information through the OLE DB provider. However, you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task.
Variable Evaluated as Expression vs Task Expression
This updated is a reply to the following comment:
We can have a dynamic value in our variable and set it directly in our SQL Statement.
Both methods will gives the same result, but if you need to use the SQL Statement in different tasks then you should use a variable and use
EvaluateAsExpression option to avoid writing the Expression multiple time (Harder to edit the package later). Else there is no need to declare a dynamic variable just define the expression inside the Task.