I was recently introduced to a number of SSIS packages containing execute SQL and data flow tasks, all of which were using dynamic SQL. After reviewing the tasks I found that many could, and should, be rewritten using an SSIS variable. The focus of this post will be on replacing dynamic SQL in SSIS with variables and is not meant to address the good, bad, or ugly of dynamic SQL. If you want to evaluate the pros and cons of dynamic SQL PLEASE review Erland Sommarskog article that will answer any question you may have on this topic here.
Without diving into what can become a heated debate over the use of dynamic SQL I will just outline several reasons for replacing dynamic SQL with an SSIS variable.
1. First and foremost IT IS MUCH EASIER to troubleshoot and debug statements passed as variables than those using dynamic SQL
a. Dynamic SQL statements are very difficult to troubleshoot as the statement is only fully visible at runtime
b. Statements passed using SSIS variables are available during debugging and can displayed using techniques such as a script task message box or viewed in the Locals window during a breakpoint
2. SSIS statement variables provide all of the benefits of creating an ad-hoc dynamic query without the limitations of dynamic SQL
This post will focus on, and support, these benefits, as well as pointing out the limitations of SSIS variables.
Let's first focus on why dynamic SQL or SSIS variables are required. One of the most common reasons for using dynamic SQL is that the column(s) and/or table(s) need to be dynamically defined at runtime. T-SQL does not support using variables for these types of queries. The below example illustrates a query that would require using dynamic SQL since in its current form would result in an error:
DECLARE @table VARCHAR(50) = 'Person.Person'
GO Results Msg 1087, Level 16, State 1, Line 5 Must declare the table variable "@table".
To successfully execute this statement dynamic SQL can be used as displayed below using both EXEC and sp_executesql:
DECLARE @table VARCHAR(50) = 'Person.Person'
DECLARE @cmd AS NVARCHAR(4000)
SET @cmd = 'SELECT * FROM ' + @table
EXEC sp_executesql @cmd;
The above sample shows an overly simplistic, but quite common, example of dynamic SQL. SSIS provides the means of replacing dynamic SQL with variables that provide the same functionality, but are far easier to troubleshoot.
To demonstrate using variables for dynamic statements create a package and two variables: Statement Data Type String
TableName Data Type:String Value:Person.Person
Click the ellipse button under the Expression column for the Statement variable and enter the following expression:
"SELECT FirstName, MiddleName, LastName FROM " + @[User::TableName]
With the statement defined and the data source configured to use it you need only add a destination to send the query results.
This seems simple enough, and in fact a bit too easy. The point of using a variable as a statement is to be able to dynamically create a query, which the above sample is somewhat lacking. To better demonstrate a dynamically generated statement add an execute SQL task to the control flow pane and set the Result Set to Single row and use the query below for the SQLStatement value:
SELECT SCHEMA_NAME(schema_id) + '.' + name AS name
WHERE SCHEMA_NAME(schema_id) = 'Person'
AND OBJECT_NAME(OBJECT_ID) = 'Person'
In order for the TableName variable to be assigned the value from the query configure the Result Set property to assign the output value:
Using an SSIS variable as a statement provides the ability to dynamically generate a statement without using dynamic SQL and is far easier to troubleshoot. Unlike dynamic SQL SSIS variable values can be viewed during debugging of a package allowing you to see the statement that will be used in either an execute SQL or data flow task:
To keep true to my word I wil mention one limitation of using a variable as a statement is that there is a maximum of 4,000 characters in an expression, which can make it a bit trying when working with a rather large and complex dynamic statement. Please keep in mind that this limitation is for expressions and not for string variables, which means that it is possible to create multiple string variables, assign their values in each expression, and then use a variable to concatenate all of the variables together. The below illustrates where 4 different variables were created all defining an expression of up to 4,000 characters. Finally a variable called FullStatement concatenates each one of the variables together.
Kind of a pain and can definitely make using variables as a value a bit more complex, but it works all the same.
The sample package referenced in this post can be downloaded from my site here.