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