Overview: Examples of conditional operators (aka if-then-else) within an SSIS derived column transformation, including syntax for nesting multiple conditions. A conditional operator is the SSIS expression equivalent of an IF, IIF, CASE, or Switch statement.
Conditional Operator Syntax
The ? and : are referred to as conditional operators. This syntax originated in C and C#, as does much of the SSIS expression language. Some sites & bloggers refer to this syntax as a ternary operator (i.e., a conditional operator that takes 3 operands).
Boolean Condition (If) ? True Expression Result (Then) : False Expression Result (Else)
Simple If…Then Examples
With an Equal Condition:
Field == "TrueCondition" ? "True" : "False"
With a Type Cast:
(DT_STR,50,1252)(Field == "TrueCondition" ? "True" : "False")
With an OR Condition:
Field == "TrueCondition1" || Field == “TrueCondition2” ? "True" : "False"
With an AND Condition:
Field1 == "TrueCondition1" && Field2 == “TrueCondition2” ? "True" : "False"
Checking for a Null:
ISNULL([Amount]) ? 0 : [Amount]
Nested If…Then Examples
Nested with an Equal Condition:
Field == "TrueCondition1" ? “True” : ( Field == "TrueCondition2" ? “True” : ( Field == "TrueCondition3 " ? “True” : ( Field == "TrueCondition4" ? “True” : “False" ) ) )
Nested with a Type Cast:
(DT_STR,3,1252)Field == "TrueCondition1" ? "True" : (Field == "TrueCondition2" ? "True" : (Field == "TrueCondition3" ? "True" : "False" ) )
Nested Check for a Null in Numerator and Denominator:
ISNULL([Income]) ? [Expense] * -1 : ( ISNULL([Expense]) ? [Income] : [Income] – [Expense] )
Finding More Information
?: Conditional (SSIS Expression)