Home  >  Blog  >   SSIS

Conditional Expression In SSIS

Rating: 4
  1. Share:
SSIS Articles

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

Learn how to use SSIS, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll for Free SSIS Training Demo!

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.

 MindMajix Youtube Channel

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:

 SSRS Power BI
 SSAS SQL Server
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator


Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
SSIS TrainingApr 27 to May 12View Details
SSIS TrainingApr 30 to May 15View Details
SSIS TrainingMay 04 to May 19View Details
SSIS TrainingMay 07 to May 22View Details
Last updated: 03 Apr 2023
About Author

Ravindra Savaram is a Technical Lead at Mindmajix.com. His passion lies in writing articles on the most popular IT platforms including Machine learning, DevOps, Data Science, Artificial Intelligence, RPA, Deep Learning, and so on. You can stay up to date on all these technologies by following him on LinkedIn and Twitter.

read more
Recommended Courses

1 / 15