SSIS #96 – Everybody Needs Integration Services Expression Reference
In one of my many SSIS blog posts, I said that if you are using SSIS, sooner or later, you are going to create variables.
In this blog, I’ll say that, if you are using SSIS, sooner or later, you are going to be frustrated by the crazy syntax in Expressions.
If you are like me, who has been in the database world for awhile, SQL-like syntax becomes intuitive to us over the time, but the VB-like syntax used in the SSIS expressions can be very foreign to us. Logical AND needs to be &&, logical OR needs to be ||, concatenation needs to be &, and == is not the same as =.
OK, here is the official link to the Integration Services Expression Reference on MSDN.
So, what are Expressions? To me, Expressions in SSIS are SISS developers’ “programming” language. Developers who have mastered tools like C# can write programs that can meet any (almost) user requirements with the ease of conditionally and dynamically controlling logic flows and creating logic branches. Compared to those application programmers, we, database developers, often look very awkward, and inadequate sometimes, in terms of being in control.
Fortunately, in SSIS, we can make friends with Expressions. From the above link in MSDN:
Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.
Did you notice that it says expressions are complex frequently?
There are 4 topics on the MSDN reference site.
Describes expression evaluator syntax, the data types that the Data Transformation Pipeline uses, data type conversion, and expression elements.
Describes the operators that the expression evaluator provides.
Describes the functions that the expression evaluator provides.
Provides expressions that use multiple operators and functions.
The Functions section can be very familiar to many database developers. To be able to feel comfortable with using Expressions in SISS, everybody needs to read the Operators section and the Advanced section at lease once.
So, next time, when you cannot get your simple Precedence Constraint working as the way you wanted it to, check out the above Integration Services Expression Reference first.
Just a side note. This blog is inspired by my late night change to a SSIS package. I wanted to add a Precedence Constraint so that my package will not run if it’s a holiday or weekend.
Here is a “simple” expression ( simple only after I remembered AND needed to be &&):
@varHolidayInd == "N" && @varWeekendInd == "N"