Conditional Expression In SSIS

You use the conditional expression operator to build logical evaluation expressions in the format of an IF..THEN logical structure:

Boolean_expression ? expression_if_true : expression_if_false

The first part of the operator requires a Boolean expression that is tested for a true or false return value. If the Boolean expression returns true, then the first expression after the ternary operator (?) will be evaluated and returned as the final result of the conditional expression. If the Boolean expression returns false, then the expression after the separation operator (:) will be evaluated and returned. Both expressions, as operands, must adhere to one of the following data type rules:

• Both operands must be numeric data types that can be implicitly converted.
• Both operands must be string data types of either Unicode or non- Unicode. Each operand can evaluate to separate types — except for the issue of setting explicit NULL values. In that case, the NULL value for DT_STR non-Unicode NULL values must be cast.
• Both operands must be date data types. If more than one data type is represented in the operands, the result is a DT_DBTIMESTAMP data type.
• Both operands for a text data type must have the same code pages.

If any of these rules are broken, or the compiler detects incompatible data types, you will have to supply explicit casting operators on one or both of the operands to cause the condition expression to evaluate. This is more of an issue as the conditional expression is compounded and nested. A typical troubleshooting issue is seeing an incompatible data type message resulting from a comparison deep in a compound conditional expression. This can be the result of a column that has changed to an incompatible data type, or a literal that has been provided without a suffix consistent with the rest of the expression. The best way to test the expression is to copy it into Notepad and test each piece of the expression until the offending portion is located.

Casting issues can also create false positives. You can see casting truncation in the following example of a Boolean expression comparing the datetimestampoffset and a date value:

Frequently Asked SSIS Interview Questions & Answers

(DT_DBDATE) “2014-01-31 20:34:52.123 -3:30″ == (DT_DBDATE)”2014-01- 31”

Casting converts the expression (DT_DBDATE) “2014-01-31 20:34:52.123- 3:30” to “2014-01-31”, causing the entire expression to evaluate to true. Date and time conversions are one example of casting issues, but they can occur on any data type that allows forced conversion.

List of Related Microsoft Certification Courses: