SSIS Expression Language and Variables
Introduction
This post is part of a series titled An Introduction to the SSIS Expression Language.
In this post, I demonstrate the SSIS Expression Language and Variables.
To build the demo project described in this article you will need SSIS 2005 and the AdventureWorks sample database (which can be downloaded at http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004).
Build The Demo Project
If you have not already done so, create an SSIS project named ExpressionLanguageDemo.
Once the project is created, open Solution Explorer and rename Package.dtsx - change the name to Variables.dtsx. When prompted, rename the package object as well. (I think you should always do this - I always answer this message box by clicking the Yes button.)
Make Some Variables
Click the SSIS dropdown menu and select Variables.
When the Variables window displays, click the Add Variable button (first button on the left) to create a new variable with the following properties:
-
Name: SQL
-
Scope: Variables
-
Data Type: String
-
Value: Select 1 As One
I included a syntactically-correct Transact-SQL statement as the default value. Why? Later I plan to use this statement in an Execute SQL Task. I chose to include a valid default to avoid validation warnings and errors.
Validation: SSIS provides design-time and run-time validation of components and settings. In general, this is a good thing as it catches real and potential errors before the SSIS package is executed. SSIS also provides a means of ignoring design-time validation warnings and errors via the DelayValidation property.
Add Some Tasks
Drag a Script Task onto the Control Flow and double-click to open the editor. If you're using SSIS2008, set the ScriptLanguage property on the Script page to Microsoft Visual Basic 2008. (If you're using SSIS 2005 you have no other option.) Add SQL to the list of ReadOnlyVariables and click the Design Script (Edit Script in SSIS 2008) button to open the script engine editor.
Replace the code in Public Sub Main() with the following:
Public Sub Main()
Dim sSQL As String = Dts.Variables("SQL").Value.ToString
MsgBox("SQL: " & sSQL)
Dts.TaskResult = Dts.Results.Success
End Sub
Close the Visual Studio for Applications editor and click OK to close the Script Task editor.
Execute the package and observe the result. You should see a message box displaying the value of the SQL variable:
Just the SQL, Ma'am
Next, add an Execute SQL Task to the Control Flow canvas and connect a Precedence Constraint from the Script Task to the Execute SQL Task. Double-click the Execute SQL Task to open the editor. Leave the ConnectionType property set to OLE DB. Click the dropdown for the Connection property and click "":
When the Configure OLE DB Connection Manager form displays, select a connection to the AdventureWorks database if one exists in your Data Connections list. If not, click the New button. Configure the connection to your server - I use (local) for my default local instance - and the AdventureWorks database:
Click OK until you return to the Execute SQL Task editor. You have three options for the SQLSourceType property: Direct input, File Connection, and Variable. There is a limit to the number of characters you can enter using direct input. I'm not sure but I think it's around 4,000 or so. I've hit this limit once - and in a big way: the client required 4 MB of dynamic SQL. I would add italics to that statement if it didn't make it look so funny. After unsuccessfully lobbying for a better approach, I managed the dynamic SQL in a script task that wrote it to a file, and used a file connection SQLSourceType to execute it.
For our example, set the SQLSourceType to Variable. Then set the SourceVariable property (which was hidden until you selected the SQLSourceType Variable) to User::SQL.
Click OK to close the editor and execute the package to test. Click OK when the message box displays. The Execute SQL Task should succeed:
Make it Dynamic
First, we'll add some more variables and build a dynamic SQL statement the old school way.
Create the following package-scoped String data type variables [with default values]:
-
SelectClause [SELECT Title, FirstName, LastName, EmailAddress]
-
FromClause [FROM Person.Contact]
-
WhereClause [WHERE LastName IN ('Smith','Jones')]
Open the Script Task editor and navigate to the Script page. Move the SQL variable from the ReadOnlyVariables property to the ReadWriteVariables property. Add SelectClause, FromClause, and WhereClause to the ReadOnlyVariables property:
Click the Design Script button and replace the code in Public Sub Main() with the following:
Public Sub Main()
Dim sSelect As String = Dts.Variables("SelectClause").Value.ToString
Dim sFrom As String = Dts.Variables("FromClause").Value.ToString
Dim sWhere As String = Dts.Variables("WhereClause").Value.ToString
Dim sSQL As String = sSelect & " " & sFrom & " " & sWhere
Dts.Variables("SQL").Value = ssql
MsgBox("SQL: " & sSQL)
Dts.TaskResult = Dts.Results.Success
End Sub
Close the VSA editor and click OK to close the Script Task editor. Execute the package to examine the results:
Express Yourself!
Let's look at another way to accomplish the same result, this time using expressions.
First, edit the Script Task Public Sub Main() code to read:
Public Sub Main()
Dim sSQL As String = Dts.Variables("SQL").Value.ToString
MsgBox("SQL: " & sSQL)
Dts.TaskResult = Dts.Results.Success
End Sub
This returns the script task functionality to simply displaying the value of the SQL variable.
In the Variables window, click on the SQL variable and press the F4 key to display the properties of the SQL variable. Change the EvaluateAsExpression property to True and enter the following expression in the Expression property:
@SelectClause + " " + @FromClause + " " + @WhereClause
This changes the way the SQL variable works. It no longer contains the value specified in the Value column of the Variables window. Instead, the value of the SQL variable is determined by the expression, which contains the SelectClause, FromClause, and WhereClause variables.
Conclusion
The SSIS Expression Language can be used with variables to dynamically set the value of one variable from one or more other variables.
:{> Andy