Home / SSIS

SQL Server Boolean Expression In SSIS

Rating: 4.0Blog-star
Views: 6743
by Ruchitha Geebu
Last modified: February 13th 2021

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:

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


About Author

NameRuchitha Geebu
Author Bio

I am Ruchitha, working as a content writer for MindMajix technologies. My writings focus on the latest technical software, tutorials, and innovations. I am also into research about AI and Neuromarketing. I am a media post-graduate from BCU – Birmingham, UK. Before, my writings focused on business articles on digital marketing and social media. You can connect with me on LinkedIn.