SQL Server Boolean Expression In SSIS

Boolean expressions evaluate to either true or false. In their simplest implementation, precedence constraints use Booleans expressions as gatekeepers to determine whether or not an operation should occur. Within Data Flow operations, Boolean expressions are typically employed in the Conditional Split Transformation to determine whether a row in a Data Flow should be directed to another output.

SQL Server Boolean Expression

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!

For example, a Boolean expression to determine whether a Control Flow step would run only on Friday would require code to parse the day of the week from the current date and compare it to the sixth day, as shown here:

DATEPART( “dw”, GETDATE() ) == 6

This is a useful Boolean expression for end of the week activities. To control tasks that run on the first day of the month, use an expression like this:

DATEPART (“dd”, GETDATE() ) == 1

This expression validates as true only when the first day of the month occurs. Boolean expressions don’t have to be singular. Compound expressions can be built to test a variety of conditions. Here is an example in which three conditions must all evaluate to true in order for the expression to return a true value:

BatchAmount == DepositAmount && @Not_Previously_Deposited == True &&
BatchAmount > 0.00

The @Not_Previously_Deposited argument in this expression is a variable; the other arguments represent columns in a Data Flow. Of course, an expression can just as easily evaluate alternate conditions, like this:

(BatchAmount > 0.00 || BatchAmount < 0.00) &&
@Not_Previously_Deposited == True

In this case, the BatchAmount must not be equal to 0.00. An alternative way to express the same thing is to use the inequality operator:

BatchAmount != 0.00 && @Not_Previously_Deposited == True

Don’t be tripped up by these simple examples. They were defined for packages in which the data had known column data types, so there was no need to take extra precautions with casting conversions. If you are dealing with data from less reliable data sources, however, or you know that two columns have different data types, then take casting precautions with your expression formulas, such as in this expression:

(DT_CY)BatchAmount == (DT_CY)DepositAmount &&
@Not_Previously_Deposited ==
True && (DT_CY)BatchAmount > (DT_CY)0.00

The Boolean expression examples here are generally the style of expression that are used to enable dynamic SSIS package operations. We have not covered the conditional, date/time, and string-based Boolean expressions, which are in the following sections. String expression development requires a little more information about how to handle a NULL or missing value, which is covered next. You can see some examples of these Boolean expressions put to work at the end of this Topic.

Frequently Asked SSIS Interview Questions & Answers

List of Related Microsoft Certification Courses: