A little fun with SSIS expression language

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 is lacking (as far as I can tell – correct me if I’ve missed something) is the ability to use an IF…ELSE IF…ELSE statement.

So I ran into a situation earlier in which I needed to do an advanced conditional expression.  Here’s the situation:  My input has a column named provider.  This column comes to me as an integer 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.

So just for fun, I’ve posted the expression below.  It’s actually three conditional operators working together to provide an advance IF…ELSE IF…ELSE statement.  [For the record and in case it’s not clear from the code below, the syntax for the Conditional Operator is (value_to_test) ? (value_to_return_if_true) : (value_to_return_if_false).]

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

Edit 6/26/2017: There’s 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)

About the Author

Tim Mitchell
Tim Mitchell is a data architect and consultant who specializes in getting rid of data pain points. Need help with data warehousing, ETL, reporting, or training? If so, contact Tim for a no-obligation 30-minute chat.

Be the first to comment on "A little fun with SSIS expression language"

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.