BlackCatBone


Greetings,

I have a question about the best method to ensure the proper flow of control within a package. I have three related SQL tasks 每 A, B, and C 每 that must execute in sequence. If any one of the tasks fails then the remaining tasks should not execute because each is dependent on the previous step. (A ※success§ precendence constraint.)

Likewise, I have a second set of SQL tasks 每 1, 2, and 3 每 that must execute in sequence. Task 3 should never execute unless tasks 1 and 2 have been successful.

I consider these to be two independent logic paths in the same package and, indeed, when I run the package both task A and task 1 begin running at the same time.

What I*m not sure of is how to add a new task 每 Z 每 that will execute only after both the A-B-C and 1-2-3 task paths have completed. Diagramatically, this would be like having a ※completion§ constraint linking task C to task Z and another ※completion ※ constraint linking task 3 to task Z. In other words, the two independent paths merge at task Z. Task Z is an audit step that must always run 每 it emails summaries of the database activities performed by the other tasks.

What has me puzzled is how I get to Z when there is a failure in either the A-B-C path or the 1-2-3 path. As things stand now, the package will never reach task Z if there is a failure in an earlier task because they are joined by a ※success§ constraint.

I thought I could get around this by using a ※sequence container§ but I discovered when I added A, B, and C to a container that I couldn*t use the ※success§ constraint I needed. I was just guessing that this might be the way to do it.

Can anyone help me with a best practice approach to the control flow issue I face I*m new if that isn*t apparent by my question, so don*t be afraid to make your answer simple and plain.

Thanks for any help,

BCB




Re: How do you handle this control flow issue, asked Mr. Newbie

Phil Brammer


Use "Completion" instead of "Success." Also set MaximumErrorCount to 999 or something arbitrarily high so as not to stop package execution when a task fails.






Re: How do you handle this control flow issue, asked Mr. Newbie

BlackCatBone

Thanks for your quick reply!

But I'm still puzzled. To simplify my earlier example, I've got sequential tasks A, B, C, and Z. C should execute only if A and B are successful so I've linked A, B, and C with a "success" constraint. Z should always execute so I have a "completion" constraint between C and Z. However, if A or B fail then the package currently does not not advance to C (which is good) and therefore does not make it to Z either (which is bad).

Basically, I need a way to treat A, B, and C as a logical unit that should always advance to task Z regardless of whether the A-B-C "unit" succeeds (makes it through task C) or fails.

Regards,

BCB






Re: How do you handle this control flow issue, asked Mr. Newbie

Phil Brammer

Put A-B-C into a sequence container. Put 1-2-3 into another sequence container. Link the precedence constraints of the sequence containers to Z.

Make sure that MaximumErrorCount is still set high enough to not croak when there is an error, and ensure that tasks A,B,C,1,2,& 3 do not have FailParentOnFailure set to true.





Re: How do you handle this control flow issue, asked Mr. Newbie

BlackCatBone

Hello Phil,

Thanks again for your reply. I have placed the A-B-C tasks within a sequence container and linked the container with a "completion" constraint to task Z. I see that the container is doing part of what I need - it is treating tasks A, B and C as a unit. However, I don't seem to be able to control the execution sequence of A, B and C within the container - they run at the same time and that won't meet our business requirements. Each task builds on the previous task so B can't run until A completes and C can't run until B ends.

I tried to add a "success" constraint between tasks A and B and tasks B and C but precedence constraints do not seem to be allowed within a sequence container. At least I don't see the task names when I select the "Add Precedence Constraint" option after dragging my tasks to the sequence container.

How can I control the task execution sequence with the sequence container

Thanks for your help.





Re: How do you handle this control flow issue, asked Mr. Newbie

Phil Brammer

You're looking too hard. Drag the green arrow from task A to task B. That is, select task A, and then the green arrow will appear. Repeat for B to C.





Re: How do you handle this control flow issue, asked Mr. Newbie

BlackCatBone

Phil,

Thanks for your help. Everything is working properly.

Regards,

BCB