In this SSIS Tutorial we will cover IF Statement.
SSIS IF Statement is a very common task when we use Data Flow Tasks and you often perform it using Derived Column Transformation.
You may have done IF Expressions in SSRS, Excel or T-SQL, but how to do IF statement in SSIS? That's what I will show you in this article.
SSIS IF Statement common examples:
SSIS IF Examples using derived column
Below are some examples of SSIS IF statement with various scenarios which you can put inside your SSIS derived column. For the purpose of this SSIS Step by step tutorial we will use SSIS 2008 R2 but these examples should work with SSIS 2005, SSIS 2012 and 2014.
Before we dive in let's discuss the syntax.
Many people may find a the syntax a bit odd at the beginning (like myself) unless you are experienced with c# programming but nothing to worry about I will explain each element which should make it much easier to understand.
Below is the syntax:
Below are some remarks:
1) So instead of using IIF function we don't put anything! but still use brackets (if you want).
2) Inside bracket we have condition and operators are also different! so instead of = we should use ==
3) After the condition we need to put ?
4) After ? we put the true part so if the condition is met it will return anything we but here
5) After TruePart we need to use : and put False Part and close parenthesis.
That's it! Let's look into some examples.
SSIS IF with one simple condition. In our simple example below we will check if FieldName equals 1 (remember to use ==). If yes then put "true" else "false"
(FieldName == 1 ? "true" : "false")
SSIS IF with two conditions and using OR operator. In our example below we will check if FieldName equals 2 OR FieldName equals 3. We will return yes if true else false.
(FieldName == 2 || FieldName == 3 ? "true" : "false")
SSIS IF with two conditions and using AND operator. FieldName equals 2 AND FieldName equals 3
(FieldName == 2 && FieldName == 3 ? "true" : "false")
Date conditions - Particular date and date range
SSIS IF with one simple condition but this time based on date. This is a simple example, you might sometimes need to cast it to date or use DateDiff function.
(DateField == "2010-03-01" ? "true" : "false")
SSIS IF with two conditions this using date range. This is a simple example, you might sometimes need to cast it to date or use DateDiff function.
(DateField >= "2010-03-01" && DateField <= "2010-03-02" ? "true" : "false")
Below is screen shot with results
Another common question is how to do SSIS CASE or nested IF using derived column. The answer to CASE is use nested IF and below is simple real-life example
Let say we want to return different percentage depending on the range of date:
Anything below 01 Jan 2011 is 0.5 between 01 Jan 2011 and 28 Feb 2011 is 0.75 and anything else is 1
(DateField < "2011-01-01" ? 0.5 : (DateField >= "2011-01-01" && DateField <= "2011-02-28" ? 0.75 : 1))
Below are some related links:
SSIS IF (Microsoft)
Hope that helps!