SSIS: Using IF(IIF) logic in Derived Column

Arie D. Jones

Sep 30, 2008 • 1 min read

Just a short reminder for myself really. A lot of people tend to use a custom script in order to accomplish this logic simply because the interface for the derived column data object simply does not spell out how to accomplish this. In order to place an IF statement for the derived column you would use something like the following syntax

({Boolean Expression}?{True Part}:{False Part})

So if I were checking against a product_type column to determine whether I should use wholesale_price or retail_price I could simply write up something like the following.

([product_type]==2?[wholesale_price]:[retail_price])

Another benefit to using the derived column data object instead of a custom script task is that the process is so much faster. In my personal experience with large ETL sets of data substituting the derived column for the custom script task, cuts my data processing for that event in more than half. So play around with it and see what you can accomplish. If the fast processing of data is key for your work, then you might also want to learn more about stream processing, or otherwise instant processing of events and the data being recorded. If you’re interested in instant data processing applications, you can learn more here.

Cheers,
AJ

P.S.: In order to use multiple entries you would just need to expand the syntax. So in Tim’s case if product_type = 2 then x else if product_type = 3 then y else z (please see comments…). You would merely have to use the following:
([product_type]==2?[x]:([product_type]==3?[y]:[z]))
You just have to make sure that you use the parenthesis so that the evaluation stays true to what you intended! Hope this helps!

Update: So the question was if how you would use the substring statement in the body of you iif statement to check if the column was blank…if it is then set if to null otherwise set it to the substring value.
Basically, it would be like this. Please note that I have used a different form to test for a blank field. In my version I use Trim and LEN(gth) functions so that the line can be a thousand blank spaces and I will get the same result..my column name in this instance is “Test”
(LEN(TRIM(Test)) > 0 ? SUBSTRING(Test,1,5) : NULL(DT_WSTR,5))

Sign up for more like this.