Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of experience. Tim is the principal of Tyleris Data Solutions and is a Linchpin People teammate. Tim has spoken at international, regional, and local venues including the SQL PASS Summit, SQLBits, SQL Connections, SQL Saturday events, and various user groups and webcasts. He is a board member at the North Texas SQL Server User Group in the Dallas area. Tim 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 twitter.com/Tim_Mitchell.

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)

Comments

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 ..!

Leave a Comment

Please register or log in to leave a comment.