The Logic of IF...THEN

Need the simplicity of the IF...THEN statement in your SSIS packages? It can be done.

One of the questions I am regularly asked is how to build IF...THEN logic into an SSIS package. While there are no built-in tasks or transformations that provide you an IF...THEN style comparison, there are a couple ways to make this happen. If you need this logic in your Control Flow, you can use Precedence Constraints and if you need it in your data flow, you use Conditional Split.

For Control Flow, the easiest thing to do is make your logical comparison in a script or with an expression and then set a variable value to use as an answer flag. For example, say you want to check for the existence of a particular file on disk. You can do that via a script task and then set an SSIS variable to True or False to indicate whether there are files. Then you can just edit the Precedence Constraint following the script to use an expression that checks your variable value (see Fig. 1). You can build as many paths after the script as you need. This allows you to mimic not only IF...THEN logic but also CASE logic.

Precedence Constraint dialog
Figure 1. To mimic an IF...THEN in the SSIS package, edit the Precedence Constraint that follows the script. (Click image to view larger version.)

Inside a Data Flow, the easiest thing to do is to use the Conditional Split transformation. I won't go into a lot of detail, but this transformation allows you to evaluate the data in your package and split it up based on values. In other words, you are able to send data down multiple paths based on the values in the data. Alternatively, you could write a custom transformation script if you feel you need something more complex.

While there are no tasks or transformation explicitly called IF...THEN, you can functionally create the same logic using the tips in this post. I hope this makes your SSIS package development just a little easier.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at


  • What Money in Excel Means for the Future of Microsoft 365 Apps

    Microsoft's new personal finance tool hints at what's in store for next-generation Office applications, from more third-party integrations to subscription requirements.

  • Microsoft Buys Orions Systems To Enhance Vision AI Capabilities in Dynamics 365

    Microsoft announced on Tuesday that it has acquired Orions Systems with the aim of enhancing Dynamics 365 capabilities, as well as the Microsoft Power Platform.

  • Microsoft Hires Movial To Build Android OS for Microsoft Devices

    Microsoft has hired the Romanian operations of software engineering and design services company Movial to develop an Android-based operating system solution for the Microsoft Devices business segment.

  • Microsoft Ending Workflows for SharePoint 2010 Online Next Month

    Microsoft on Monday gave notice that it will be ending support this year for the "workflows" component of SharePoint 2010 Online, as well as deprecating that component for SharePoint 2013 Online.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.