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
Subscribe to our youtube channel to get new updates..!
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.
List of Related Microsoft Certification Courses:
|SCCM||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|