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.
- SSIS Expression Language and Variables
- SSIS Expression Language and the Derived Column Transformation
- SSIS Expression Language and the Conditional Split Transformation
- SSIS Expression Language and Dynamic Property Expressions
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
Comments