SSIS: Using IF(IIF) logic in Derived Column

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.

Cheers,
AJ

P.S.: In order to use multiple entires you would just need to expand the sytax. 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))

Powered by ScribeFire.

Leave a Reply

Your email address will not be published. Required fields are marked *