Introduction
I sometimes miss the ActiveX task in DTS. It was cool because "if you could see it, you could change it." I used the ActiveX Task to generate dynamic SQL.
When To Use Dynamic SQL In SSIS
There are lots of uses for dynamic SQL in SSIS. If you find yourself using the same SQL statements over and over again with minor modifications - a different table name here, a different criteria value there, etc. - the statement is a likely candidate for dynamic SQL.
The flexibility of dynamic SQL coupled with the flexibility of SSIS allow for some very creative solutions. You can generate SQL based on environmental conditions - whether or not a directory exists; or using a Loop Container, set numeric criteria based on the value of the iterator; or in a ForEach Loop Container you can acces and include filenames in dynamically-generated SQL. The possibilties are limited only by our experience and knowledge of SSIS.
Variable On Variable Technology!
One of the best methods for generating dynamic SQL in SSIS is to use variables and utilize the EvaluateAsExpression property to update the value of the variable containing the dynamic SQL. This SSIS Design Pattern demonstrates how to generate dynamic SQL using the EvaluateAsExpression property of variables.
To start, open Business Intelligence Developer Studio (or Visual Studio - they're the same). Create a new Integration Services project. Right-click any whitespace on the Control Flow canvas and click variables:
When the Variables dialog displays, click the Add Variable button on the Variables menu bar:
Name the variable sSQL and set the Data Type to String. Set the value to "Select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress FROM Person.Contact WHERE ContactID = 0". sSQL is our template variable - we will use this initial value (that returns no rows) as the basis of our dynamic SQL.
Note: In this example I only configure and display the dynamic SQL - I do not assign it to an ExecuteSQL (or some other) Task. But if I did, the SQL would have to parse or the task would throw a validation error. There are two ways around this error:
-
Use a valid statement in the template SQL.
-
Set the DelayValidation property of the ExecuteSQL Task to True.
Create another variable of Int32 Data Type named iContactID. iContactID will contain the dynamic portion of outr SQL statement. Set iContactID's Value to 11:
Click on sSQL and press the F4 key to view the properties for sSQL. Set EvaluateAsExpression to True.
Click Expression, then click the ellipsis. In the Expression Builder, enter "Select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress FROM Person.Contact WHERE ContactID = " + (DT_STR, 4, 1252)@[User::iContactID].
We are building a string variable, but iContactID is an Int32 data type. To concatenate it to a string, we need to cast the integer to a string.
In SSIS Expression Language, casting is accomplished using Type Casts. Drag (or type) the cast function just in front of the variable, i.e. (DT_STR, 4, 1252) @[User::iContactID]. All together it will look like the following:
Click the Evaluate Expression button to test. Your SQL statement should display in the two-line label above the button. Then click Ok to exit the Expression Builder.
A note about building the expression: I am sometimes tempted to use the REPLACE string function in Expression Builder to build dynamic SQL for variables. This usually turns out to be a bad idea because the value of the variable itself is updated when it's evaluated as an expression. This means if I have a variable named sSQL with an initial value like "SELECT <ReplaceMe> FROM Person.Contact" and I use an expression like REPLACE(@sSQL, "<ReplaceMe>", "ContactID") it will work just fine on the first pass, but will fail to update the variable value on subsequent passes because the Search Phrase is no longer contained in the value of @sSQL - it was updated when the variable was evaluated as an expression.
Add a Script Task to the Control Flow and name it "Popup sSQL". On the Script screen add sSQL to the ReadOnlyVariables list:
Click the Design Script button and add a MsgBox to popup the value of sSQL:
Close the Script Task. Right-click the Script Task and click Execute Task:
The value of sSQL should popup in a message box - with the value for iContactID displayed in the WHERE clause:
Taking It To The Street
Although I do not demonstrate it in this post, you would use something similar to this example in a Loop Container. If you use iContactID as the iterator of the loop, you could start at some value and advance to some other value, executing dynamic SQL inside the loop on each pass.
Conclusion
I hope this helps you design dynamic SQL in SSIS. If you still need the advanced functionality of a script you can use the Script Task.
Download the code (Registration required)!
:{> Andy