SSIS expression for IIF

Most people starting out in SSIS find the expression language a bit awkward and unintuitive.  Sometimes you just put two things next to each other and viola you get what you want; sometimes it is a bit more complicated than that.  When working with strings you frequently have to specify a code page (What is a code page anyway?  I know, so please don’t tell me.  But for the uninitiated, it is just another obscure thing to worry about.) and length.  Getting proficient at the SSIS expression language does come with its own special pain.  One of the things that many people stumble on is IIF style logic.  After all there is no IIF expression in SSIS.  Or is there?

Well, if you are looking for IIF, or IF, you are not going to find it.  But there is conditional logic that is functionally the same.  Let’s take a look.

A normal IIF is in the format IIF(Boolean conditional statement evaluating to true or false, Value to return when the Boolean is true, value to return when the Boolean is false).  You can also nest your IIF statements as well.  IIF(Boolean expression, IIF(Boolean Expression, Inner True, Inner False), Outer False).  In this case the green outer Boolean expression true condition will be filled by the red inner Boolean expression.  You can get pretty complicated in nesting these types of IIF expressions, although personally I start to have a hard time keeping track at about 4 or 5 layers deep.  For anyone that has been around computers for a while this kind of logic should not be anything new.

So how do we turn the friendly IIF we know and love into an SSIS expression?  Basically the structure is very similar, it is just the grammar/syntax that is different.  It is still a Boolean condition followed by a true result followed by a false result.  OK, so here it is:

(Boolean expression?ReturnTrueValue:ReturnFalseValue) 

Hmm, that isn’t really complicated at all.  Is it?  There is no IIF in front, but there is the Boolean conditional expression.  The Boolean expression is followed by a question mark (?) and the ReturnTrueValue and the ReturnFalseValue are separated by a colon (:).  So really we just drop the IIF, substitute a “?” for the first comma and a “:” for the second comma in the standard IIF expression shown above and we have our SSIS expression. 

Let’s look at some real expressions using the AdventureWorks 2008 database.  Suppose we want to classify an order as big or small depending on the value stored in the Sub Total column.  If the order is over $1000 it will be classified as Big, if not it will be classified as Small.  This expression will do the trick:

Just like an IIF expression you can also next these expressions in SSIS.  Let’s take this order classification to the next layer and say that all orders over $1000 are large, all orders over $500 are medium, and the rest are small.  Here is the expression for that:

In the case of nested expressions, they behave as you might expect, the expressions are evaluated in the order they are encountered.  So in this case a $1200 order will not be classified as Medium because it already met the first condition of Big, and the evaluations stopped at that point. 

I hope you found this tip useful.  Thanks for reading.