Today it will be a concise post, but I hope quite helpful and informative.
As most SSIS developers know SSIS Expressions are used to drive several key tidbits of a package components as the Conditional Split, Package Variables value assignments, Derived Column Transformation, form a SQL statement for Execute SQL task, drive precedence constraints, set package properties as connection string for example and more. So it is hard to underestimate the importance of the SSIS Expressions for a SSIS developer.
However, the SSIS expressions look hard to use for some beginners yet, feel un-natural to most people. This fact does not undermine the power and flexibility of the SSIS Expression. The only hard to swallow portion in my opinion is the code bloat versa editing space ratio that limits the effectiveness of writing long expressions. Luckily, there is a free tool to help: SSIS Expression Editor & Tester! I do not see any dramatic improvements in regard to the SSIS Expression in the forthcoming SQL Server “Denali”, but it will add a number of features that made me excited, perhaps I shall dedicate a separate post on this subject, but I will mention one: Expression Task. This task will help to assign a result of an expression to a variable. So the SSIS Expressions are here to stay and important to know.
And now I will share the most commonly asked question on MSDN SSIS forum: “how do I handle or process multiple IF-ELSE conditions”?
The answer is it is simple!
As we know, the conditional statement in SSIS Expression has the following notation:
«boolean_test» ? «true_result» : «false_result»
The trick is the right part can accept yet another [nested] IF-ELSE, and then more:
@[User::MyStringVar] == "TEST1" ? "IT WAS TEST1":
@[User::MyStringVar] == "TEST2" ? "IT WAS TEST2":
@[User::MyStringVar] == "TEST3" ? "IT WAS TEST3":
"MATCH NOT FOUND"The end result of running this expression is it will check the MyStringVar contents and try to pick the first match, if the match not found it will produce “Match not found”.
This expression is good for a variable assignment, for a Derived Column transformation one should use:
[MyStringVar] == "TEST1" ? "IT WAS TEST1":
[MyStringVar] == "TEST2" ? "IT WAS TEST2":
[MyStringVar] == "TEST3" ? "IT WAS TEST3":
"MATCH NOT FOUND"It is easy to expand this expression further to more choices by just adding lines before the last assignment.
