SSIS expression language conditional operator

The SSIS expression language is a powerful yet enigmatic entity.  Once you get used to its syntax – which is part C#, part T-SQL and part *WTH?!?* – it’s actually somewhat fun to use.  However, one thing it appears to be lacking is the ability to use an if/then/else statement.

However, there actually is such an instrument, though it doesn’t look like a typical if/then/else statement. The SSIS expression language conditional operator allows for the same type of logic.

SSIS expression language conditional operator

The SSIS expression language conditional operator uses the following syntax:

(value_to_test) ? (value_to_return_if_true) : (value_to_return_if_false).

If you need more advanced behavior (such as if/else if/else if/else), you can nest these conditional operators.

For a practical example, consider this scenario: we have an input column named [provider]. This column is typed as an integer in the source but has to be padded with zeros where necessary to make it a total length of 3 (“2” becomes “002”, “13” becomes “013”, etc.).  A special case exception is the value “1” which is to be left as is.

The example I created below actually uses three conditional operators working together to provide an advanced if/else if/else statement.

(LEN(provider) < 3 && LEN(provider) > 0 && provider != "1") ? (LEN(provider) == 1 ? "00" + provider: "0" + provider) : (LEN(provider) == 0 ? "000" : provider)

Once you’ve worked with SSIS expression language for a while, you’ll find a few shortcuts. In analyzing the example above, you may find an easier way to do this. While the above example does in fact show that you can nest conditional operators, this operation is more efficiently written by using a single conditional operator and the RIGHT() function, as shown below:

(provider == "1") ? provider : RIGHT("00" + provider,3)

Conclusion

The SSIS expression language conditional operator is the way if/then/else statements are built when using the expression language in SQL Server Integration Services. While the syntax looks a bit different, this offers the same behavior as you’ll find in conventional if/then/else statements in C#, T-SQL, and other database-friendly languages.