SSIS How to make one task flow wait for another

Question

SSIS How to make one task flow wait for another

Tom Bakerman

Hall of Fame

Points: 3367
More actions
September 24, 2014 at 8:11 am

#290953

Hi all,
I have a task flow that looks something like the attached image.
The Start task executes, and then depending what happens, the flow goes down either the left or right side. Regardless of the condition set in the start task, the "Long Running Task" (LRT) executes. My question is: How to correctly set the precedent constraints at "???" ? I want Task D to start only after LRT completes AND Task C completes if it has executed. If Task C does not execute, then Task D can start as soon as LRT completes.
1 - Using OR (One constraint must evaluate to true) is insufficient because of the timing issue.
2 - Using AND (All constraints must evaluate to true) is insufficient because the precedent constraint between Task C and Task D will never get a value if the Start task sets the initial condition to True (ie, the right hand task flow doesn't execute).
So, I'm stumped. All ideas welcome.
Thanks,
Tom

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 1

I should mention: this is on 2008 R2. Also, the actual task flows are much more complicated, especially the right hand side. There are many more tasks and branches in the flow.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Tom

I think you need to change the precedence constraint between C and D so that it evaluates to True on Success OR if @Variable = True (where @Variable is the variable set in the Start task).

John

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 3

John Mitchell-245523 (9/24/2014)
Tom
I think you need to change the precedence constraint between C and D so that it evaluates to True on Success OR if @Variable = True (where @Variable is the variable set in the Start task).
John

Sorry John. The problem with this approach is #2 in my original post. If @Variable is True, then the right hand side of the flow doesn't execute, so the precedent constraint between C and D never gets evaluated, and the process just sits and waits for that evaluation.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Tom

I'm surprised that matters... the precedence constraint is set to Success OR an expression evaluating to True, so the fact that the expression evaluates to True should be sufficient. But I've never tried it. I'll give it a go if I get a chance.

John

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 5

Now I'm really confused.

I'm testing this out in BIDS, right? Watch the appropriate boxes turning green. Start, then Task B, then LRT. Then BIDS says the package execution has completed. But Task D and Task E never execute. I've got the precedent constraint set as you suggested John (Success or @Variable [ because @Variable is a boolean, I don't have to test for == True]), and the ??? is set to AND. I set a breakpoint at the OnPostExecute event for LRT, and @Variable is True as expected. What's going on?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

My non-expert idea is to create a dummy task between C and D with 2 constraints. One constraint expecting Success from C and one coming from the start expecting @Variable = True with a Logical OR for the constraints.

Then you can have Logical AND from LRT and dummy task on your task D.

I hope this makes sense.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Tom Bakerman (9/24/2014)
Now I'm really confused.
I'm testing this out in BIDS, right? Watch the appropriate boxes turning green. Start, then Task B, then LRT. Then BIDS says the package execution has completed. But Task D and Task E never execute. I've got the precedent constraint set as you suggested John (Success or @Variable [ because @Variable is a boolean, I don't have to test for == True]), and the ??? is set to AND. I set a breakpoint at the OnPostExecute event for LRT, and @Variable is True as expected. What's going on?

Yes, exactly the same happens when I try it. You must be right - the constraint is only evaluated if the preceding task executes, and since it knows that's never going to happen, it knows it's got no more to do and the package completes. Best to use Luis's solution, if it works. I've used dummy tasks before, but I couldn't work out where to put one in this instance!

John

Tom Bakerman Hall of Fame Points: 3367 More actions · Answer 8

Thank you Luis. That seems to do the trick, at least in a small test. Overnight will tell.