THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

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 "<New connection...>":

 

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

Published Saturday, January 31, 2009 12:02 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nitin Joshi said:

Very Good example and i a very simple way.....Thanks very much ANDY..Can you please provide some other good example sunign some toher transformations.

July 21, 2010 6:51 AM
 

Raj said:

Nice Example...thank you so much

November 2, 2010 10:00 AM
 

Anna said:

That is all very good, but string variable (as I found out) has a limited length, so you can't use it if the SQL query lists, let's say, 20 or more columns, or when it is very complex...

January 5, 2011 3:56 PM
 

Brian said:

'Dts.TaskResult = Dts.Results.Success

' Above line gave error and would not run. Commented it and replaced with below and the expected result was given.

Dts.TaskResult = DTSExecResult.Success

March 15, 2011 12:25 PM
 

Ed said:

Very nice....

April 14, 2011 3:38 PM
 

Viktor said:

You should become familiar with String.Format and lose the string concatenation in a script task.

Dim sSQL As String = String.Format("{0} {1} {2}", sSelect, sFrom, sWhere)

April 25, 2011 1:40 PM
 

Virendra More said:

if you want to evaluate a custom expression in script task then assign value to expression as below and then invoke value (in this example Variable is of type int32)

 Dts.Variables("Variable").Expression = "Day(GETDATE()) + Day(GETDATE())"

       MsgBox(Dts.Variables("Variable").Value)

June 1, 2011 11:59 PM
 

Paras said:

Thanks!

August 31, 2011 2:12 AM
 

Aaron said:

Is it possible to declare a variable IN an expression for local use only? Example expression (my goal is to get yesterdays date as of 19:00 hours). This expression is in a pkg user variable used as a date param throughout the pkg:

DECLARE @date datetime

SET @date = getdate() -1

(DT_STR,2,1252)MONTH( @date) + "/" +  (DT_STR,2,1252)DAY( @date ) + "/" + (DT_STR,4,1252)YEAR( @date ) + " 19:00"

September 1, 2011 3:07 PM
 

andyleonard said:

Hi Aaron,

  Not using SSIS Expression Language.

Andy

September 2, 2011 8:43 AM
 

Aaron said:

I suspected as much but figured it worth asking. I ended up going with:

DATEADD("Hh",19,(DT_DATE)SUBSTRING((DT_WSTR,50)DATEADD("d",-1,GETDATE()),1,10))

Much cleaner anyway :)

Thanks Andy!

September 2, 2011 9:47 AM
 

g00p3 said:

thanks, this helped. I added the following to remove the .xls on the file name:

REPLACE(  RIGHT(@[User::FilePath],FINDSTRING(REVERSE(@[User::FilePath]),"\\",1)-1), ".xls", "" )

April 17, 2013 2:21 PM
 

Yoseph said:

It is commendable work. It help me figure out how to automate variable valus. thak u Andy!! Hope to see more from you.

March 5, 2014 11:08 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement