SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

I knew that the *proper* way to do this was to either create a script component and drop down into VB.NET to map the value appropriately, or use a Conditional Split transform and then merge the values together.  However, a colleague had just asked an unrelated question about the ternary operator in the expression language (officially called the Conditional Operator in Redmond), and I decided to push it a bit and force this processing into the Derived Column transform using the Conditional Operator.

So just for fun, I've posted the expression below.  It's actually three ternary 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)

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and Microsoft Data Platform MVP with over thirteen years of data management experience. He is the founder and principal of Tyleris Data Solutions.

Tim has spoken at international and local events including the SQL PASS Summit, SQLBits, SQL Connections, along with dozens of tech fests, code camps, and SQL Saturday events. He is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2.

You can visit his website and blog at TimMitchell.net or follow him on Twitter at @Tim_Mitchell.


Posted by sauraviit on 16 October 2008

gud work man

Posted by BudaCli on 19 August 2009

Tx for this as it helped a lot. Is it possible to get one derived with two conditional staments on different lines going to the same derived column.

Posted by John Mitchell on 3 February 2010

Tim, I myself am struggling with the syntax for a complex expression using nested conditional operators.  For your situation, however, wouldn't it be easier to use something like this?

RIGHT("00" + provider, 3)

Posted by Tim Mitchell on 5 February 2010

John, you're right - this statement could be shortened a bit by using the RIGHT() function.  The only thing precluding us from using just a single RIGHT() function is that the value 1 is a special case and is to be left alone.  Hopefully, though, the longer syntax that I've used here is an effective demonstration of nesting these types of statements.

If you've got a specific case you're trying to solve with a complex conditional operator, feel free to ping me directly - tdmitch [at] gmail [dot] com.

Posted by vinothlilly on 2 September 2010

Hi Friends,

Pls solve my following query with example.

now i am doing project using ssis 2005. in that

1. import data from multiple resources like .xls, .xml,db

2.Read only file path from created meta data table like dil_table_met (Filetype=.xls,filepath=c:\..)

3.In that meta table, i want to read only file path and check file path which filetype like .xls or .xml and then import to excel source and to db destination , the same via for all file type

4. next read data from resources import correct data into correct table and wrong data into error table

5.for all these above condition are in loop.

pls give a example for this its very urgent

pls pls help me out for this

Posted by Ashish on 9 March 2011

Thanks man .. i was trying to figure this out in the middle of the night .. this trick saved my ass ..!

Posted by William Rayer on 10 August 2016

Based on the above, how about:

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

Leave a Comment

Please register or log in to leave a comment.