For good or ill, I've been designing the control of flow of my packages to contain a series of conditional steps (represented by containers and tasks). Sometimes the logical concept of a "step" in my design actually maps to a *cluster* of tasks. Imagine this overall process, divided into four logical "steps" (my term, not an official SSIS term):

(Please note a design requirement in this hypothetical scenario that the package needs to be highly recoverable if something went wrong during a previous execution. Also note the assumption that these "steps" are serial--one must come before the other.)

Step 1: Bootstrap - a script task that sets up a few things, like initializing variables that will be needed by downstream tasks; we want the package to fail if this fails, so we have MaximumErrorCount = 1 (the default); if you use precedence constraints in the way I will describe here, you won't need to use FailPackageOnFailure).

Step 2: Do Some Stuff Phase One (imagine perhaps an FTP task that downloads a file, followed by a script task that unzips the downloaded file, and another script that does some stuff with the extracted files; however, within the overall process, this step only needs to run if it has not already run; to run it again would waste time, and we want to account for the possibility that Step 3 failed the last time we ran and that we are recovering)

Step 2a: Imagine a script task that hangs off the end of Step 2 the job of which is to update a package level-flag variable to True and update a corresponding flag column in the database.

Step 3: Do Some Stuff Phase Two (imagine another cluster of tasks doing some work, perhaps some data flow--but this task can only run if two conditions are met: first, step 2 needs to have already run (either during this execution or a previous one), and second, a certain flag variable must be true)

Step 3a: Same idea as Step 2a: update a package level-flag variable to True and update a corresponding flag column in the database.

Step 4: Cleanup (imagine perhaps, one or more FileSystem tasks deleting some folders and files; we only want this cleanup to run if both Step 2 and 3 ran successfully, even if Step 2 ran during a previous package execution)

I'm trying my best to simplify a somewhat more complex real life system, so the details I've described are a bit contrived and hopefully not too abstract. The dilema I'm trying to illustrate is that Step 2 may not run at all because it may have already run on a previous package execution but we need to make sure that Step 3 gets the opportunity to evaluate whether *it* should run.

This can be challenging because if a precedence constraint is Success-based, it will not be evaulated if the executable it is attached to does not execute. If this is hard to follow, it may be caused by my terminology.

In my mind there are two separate but intertwined concepts at work in my hypothetical system: the "physical" concepts of execution and success and the "logical" concept of a "step" and whether or not the step "ran" (by which I mean, did the task or tasks that make up this step actually do any work).

What makes this tricky is that Step 2 must execute even if it does not run in a logical sense. The reason that Step 2 *must* execute is that if it does not, the Success-based precedent constraint connecting Step 2 and 3 will not be evaluated and Step 3 will never get its shot at the big time.

To put it another way, Step 3 needs to test the value of a package-level Boolean variable before deciding whether it should run (do its work), but in order to make that determination it needs to physically execute; if Step 2 does not execute successfully, then the Success-based precedence constraint will prevent Step 3 from ever getting a chance to evaluate against the Boolean variable whether it should run.

If all this abstraction is tortuous for you, let me try to bring it back down to the implementation level. Here's a text-based representation of the package structure (sorry, I'm not really in a position as I write this to make up a sample package and take a screen shot of it):

SCRIPT TASK (Step 1, bootstrapping)

SEQUENCE CONTAINER (Step 2, contains multiple tasks; the last two tasks in the sequence comprise Step 2a: a script task that updates a package-level Boolean variable followed by an Execute SQL task that updates a corresponding database column)

SEQUENCE CONTAINER (Step 3, contains multiple tasks, with a Success- and Expression-based precedence constraint hanging off the Step 2 container, which depends on the upstream container executing successfully and the Boolean variable referenced in the expression; Step 3a follows the same pattern as Step 2a)

SEQUENCE CONTAINER (Step 4, cleanup; contains multiple tasks; has a Success-based precendence constraint from the Step 3 container)

Here are the two techniques I am trying to communicate:

First, notice how the sequence containers enable the design principle of indirection [1] by providing guaranteed execution for Step 3 while still giving Step 2 the opportunity to determine whether it really needs to run at all (because it may have already run successfully, and perhaps the package failed at step 3 the last time and this time through we're hoping the dropped database connection that caused Step 3 to fail the last time has been fixed).

And second, there is a technique not explcitly shown here that I call the dummy script task [2], which solves a problem that is created when you introduce a sequence container for indirection to guarantee execution and continuation of downstream flow while at the same time needing to conditionally run the step. You can't implement your condition as an expression on the precedence constraint between Step 1's Script task and Step 2's container because that would, when the expression evaluates to False, prevent the *execution* of Step 2's container, which would prevent Step 3 from getting its chance.

So where do you put the test for the condition? Your indirection container has solved one problem (guaranteed execution) but in the process has indirected you out of your opportunity to test your condition. That's where the dummy script task comes in [3].

Hopefully this all made some kind of sense.

Comments welcome.

Dan

P.S. Please note that in relation to the above, a Completion-based precedence constraint between Steps 2 and 3 was not workable for me because it clashed with my ability to manage the two different concepts of SSIS's idea of "execution success" and my logical concept of "this step ran." I can't really explain it better than that right now. You may experiment with Completion-based precedence constraints and find success in this situation, but after struggling with them a bit I arrived at this container indirection technique in combination with the dummy script task technique [Array]. That said, I have found Completion-based precedence constraints useful in other situations.

Also note that if your needs are simpler than mine were, you may find SSIS checkpoints useful. Frankly, the main reason I did not try for a checkpoint-based recovery solution is that I did not want to delegate such a key requirement to a somewhat invisible mechanism that I perceive to be out of my control.