31 DAYS OF SSIS – CONTROLLING YOUR FLOW IN THE CONTROL FLOW (21/31)

Guest Blogger

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.

IN SEQUENCE

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.

FLOW WRAP-UP

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.