Remember, what I said last Friday? I’m going do it, again. Which means, I’m taking the day off. There will still be a 31 Days of SSIS post, you’re reading it right now.
This post was being written by Chris Fish (LinkedIn). He’s another of my co-workers at Digineer and and is on the team with me that works with SSIS and on SQL Server projects.
Hello, and welcome to my first attempt at writing a blog entry. I’d like to thank Jason Strate for letting some of us commandeer his platform on Fridays. The topic I am writing about is, how to deal with some odd Control Flow scenarios.
What The Flow?
I recently had a need to split the flow of tasks based on the results of an earlier task. So I went to the tool box to get the Conditional Split Control Flow Task. But wait, WTF? (that’s Why The Face, by the way) Where’s the Conditional Split? I even had Stan, a Digineer co-worker, look and he had the same reaction. There is no Conditional Split for the Control Flow! Instead, the same functionality is accomplished by adding expressions to the Precedence Constraint. Double click on the arrow between the tasks and the Precedence Constraint editor opens. Change the Evaluation operation to a value that includes “Expression” and then enter an expression that evaluates to true or false.
An annoying un-feature, is there is no expression builder available in this modal window so you have to either type in everything from memory or copy and paste from somewhere else. The expression field is case sensitive as well. After setting up this Precedence Constraint, you’ll see an by the arrow. That alerts you that the Precedence Constraint has an expression associated with it. To have a true conditional split, you’d set up other Precedence Constraints with the appropriate expressions. Keep in mind that you need to include all of the logic in your expressions to limit the precedence. The control flow will evaluate each branch as its own flow and will act as a multicast if you’re not careful. In other words all branches with expressions that evaluate to true will continue, regardless of what the other branches are doing.
For readability and another reason I’ll get into later, I prefer to add a blank Script Task that has a meaningful name in front of any Precedence Constraints with expressions, especially if it is meant to act as a conditional split.
Keeping With the Flow
Let’s go a step further. What if you need to conditionally run a task, but want to run another task after it, whether you run the conditional task or not? In other words, let’s say you have 3 tasks. Task 1 sets a variable that is used to determine if Task 2 is run. Task 3 needs to all the time, but needs to run after Task 2 if Task 2 runs.
This won’t work because if Task 2 doesn’t run, Task 3 won’t run. You could set up your tasks like this:
And use the Logical OR option in the Precedence Constraint like so:
But that won’t guarantee that Task 3 will run after Task 2 if Task 2 needs to be run. This would simply run Task 3 as soon as Task 1 is complete, regardless of what Task 2 is doing. This can be handy to force some processes to run a certain way, but doesn’t meet our requirement.
Enter the Sequence Container. By wrapping the conditional tasks in a Sequence Container, the flow will run Task 2 when it needs to and when all tasks are complete within the container, move on to Task 3. If Task 2 doesn’t run, the tasks in the container are still considered complete and flow moves on.
You probably noticed the extra Script Task at the top of the container. That is our empty task for readability again, except it is required here since you can’t have a Precedence Constraint before the first task in a container. The Precedence Constraint must be within the container instead of before the container in order to satisfy the flow logic we need.
Well there wasn’t anything earth shattering here, but hopefully this sparked some ideas and will save you some time when you need to build some logic into your Control Flows in the future. Thanks for reading.
11 thoughts on “31 Days of SSIS – Controlling Your Flow In The Control Flow (21/31)”
I hope you don’t mind if I share a link to a SSIS 2012 Precedence Constraint Tutorial that is split into 6 videos.
Katie & Emil
Thank you!! I always wondered how to do this, so it worked all the time!!
What if you kept your three tasks in a row but made the second precedence constraint be "Expression or Constraint". The expression would be the opposite of the expression used to determine if step two should run. Then either the middle task executes or your expression evaluates to true and the third task will run.
I point this out, because this is what I tried and it didn't work. Seems like it should, though.
Hi Hoyt, good question. I had to dig into this because I wondered why it didn’t work as well. I think what is going on is that since the control flow never actually gets to the second constraint, the expression is never evaluated. The “OR” would essentially work the same as an “AND” but with the constraint value set to complete instead of success. So the control flow still has to be able to get to the constraint.
BUT, if you connect task1 to task3 and add a constraint of expression only with your opposite expression, and set the Multi Constraints to OR, then it does work.
I like your idea of adding the Script Task to help document your flow; however, I would suggest you build on Jason's 'Every Which Way Outputs' article and put the logic into the script tasks and eliminate the quirkiness of the precedence constraints altogether.
Unless there is a big performance hit that you take by using this method, I may never use an expression constraint again.
Please disregard this. I was confusing the Script Task with the Script Component.
Comments are closed.