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>

An Introduction to the SSIS Expression Language

Introduction

The SSIS Expression Language is one of the steeper slopes on the SSIS learning curve. Many database professionals have never been exposed to the syntax. In this series, I provide examples that demonstrate common uses for the SSIS Expression Language.

The examples are built in SSIS 2005. Why? Not everyone has access to SSIS 2008 at this time, and the examples work in both platforms.

Syntax 

Books Online has some good information about the SSIS Expression Language.  This is an excellent place to start.

For starters, the syntax shares similarities with what I call "curly-brace" languages: C++, C#, Java, etc. It's obvious (to me at least) from the "=" operators:

  • == for comparison
  • = for assignment

Literals

Literals often prove challenging for folks unfamiliar with the syntax. Of particular interest is escape characters. The two escape sequences I'm most often asked about are double quotes and the backslash. As in curly-brace languages, the backslash is the escape indicator. This makes Windows paths loads of fun in SSIS.

For example, you think you're storing the path C:\results\new\andysdata.csv, but what you're telling SSIS is C: [Carriage Return] esults [New line] ew [Alert] ndysdata.csv. Yeah. That's gonna make for some interesting error messages! So how to address it? C:\\results\\new\\andysdata.csv works, as two backslashes together are the escape sequence for a single backslash.

If you want to include double-quotes in an expression, the escape sequence is backslash + " - \".

Casting

Casting is also unintuitive to the uninitiated. SSIS implicit conversions throw me every time. SSIS has its own set of data types, and the different data providers have their set of data types. Some interesting things happen where these data types meet.

To cast to a string (or SQL Server varchar) data type, preface the value with (DT_STR, [len], [code page]). For example, if you want to cast the integer 42 to a 2-character, en-us string; the expression is (DT_STR, 2, 1252) 42.

Conclusion

There's more to come in this series. I hope you enjoy it!

:{> Andy

Published Friday, January 30, 2009 12:01 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

 

Tim Mitchell said:

Andy, I'm glad to see this topic covered.  I catch a lot of questions on forums about the SSIS expression language, and it took me a great deal of time (mostly trial and error) to get beyond the basics.  This interesting syntax is still an enigma to many people, so I expect that you'll have a good deal of interest in this series.

Tim

January 30, 2009 5:57 PM
 

Log Buffer said:

"SSIS isn’t the newest thing around, but as Andy Leonard writes, 'The SSIS Expression Language is one of the steeper slopes on the SSIS learning curve. Many database professionals have never been exposed to the syntax.' If you’re one of those struggling your way up the curve, Andy’s Introduction to the SSIS Expression Language is for you."

<a href="Log'>http://www.pythian.com/blogs/1474/log-buffer-134-a-carnival-of-the-vanities-for-dbas">Log Buffer #134</a>

February 6, 2009 3:45 PM
 

Duane Douglas said:

This is a nice tutorial about SSIS expressions.  I moderate the MSDN SSIS forum and I often come across questions about or problems than can be solved with SSIS expressions.  Despite its power, expressions are one of the least understood and least used conventions in SSIS.  I'm confident that your tutorial on this subject could enlighten many more SSIS developers for years to come.

April 30, 2009 1:43 AM
 

Kent Krug said:

Andy,

Thanks for sharing this with me.

The problem that I'm still having is that I can change the value of a User Variable with Package Scope and view the change it break mode.

However after I stop debugging the original value persist.

In DTS the value of the Global Variable would be set to value that it was changed to when the Package was run.

Regards,

Kent

November 9, 2009 11:39 AM
 

Marty Nickel said:

A backslash doesn't escape a double quote in SSIS 2008.  That fact is much-complained around the web and it seems that custom import routines are the only solution.  To me this seems like quite a shortcoming.

February 23, 2010 9:55 AM
 

Sid said:

How would you execute the SQL expression built in your package?

March 4, 2010 5:10 AM
 

Gary Melhaff said:

Have you ever had a task not executing even though your precedence constraint is correct?  

I had this problem and thought it was because I was comparing string variables to eachother (thanks to attunity oledb doesn't support returning count into anything but string variable). The real cause?  I was trying to share an email task between 2 different sequence containers. SSIS apparently has a wonderful feature that if you do this you don't get an error - it just stops running as if it didn't meet the expression condition. Ouch - be nice if this was more widely advertised. btw...am running 2008r2

September 21, 2010 8:55 PM
 

andyleonard said:

Hi Gary,

  Are you sure the precedence constraints are configured properly?

Andy

September 21, 2010 9:44 PM
 

Jason Yousef said:

Nice article Andy, not sure what you meant by "If you want to include double-quotes in an expression, the escape sequence is backslash + " - \"." however the double quote is presented by "\"" for clarification.

November 2, 2012 10:35 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