Welcome to VSTSC's Community Server | | Help

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 .  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

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

is also unintuitive to the uninitiated.  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 Thursday, January 29, 2009 12:00 AM by andy
Filed under: ,

Comments

Thursday, February 19, 2009 7:00 PM by Applied Business Intelligence

# SSIS Expression Language and the Conditional Split Transformation

Introduction This post is part of a series titled An Introduction to the SSIS Expression Language . In

Saturday, February 21, 2009 10:21 PM by Applied Business Intelligence

# SSIS Expression Language and Dynamic Property Expressions

Introduction This post is part of a series titled An Introduction to the SSIS Expression Language . In

Anonymous comments are disabled