In the old days, I'd disbale tasks up front and then at runtime decide which
task to "goto" and enable it and then mark it for execution. This allowed me
to maintain a "state machine" wherein even a restart could determine where
the package left off and continue execution at the right "branch". I can't
figure out how to do this is in SSIS!

example:

task 1: lookup the current step somewhere (in the db, for example). Jump to
the correct task accordingly.

task n: perform work and then jump back up to the top.

Now, I've been able to get the first behavior by setting precedence
constraints that evaluate a user variable. What I can't figure out how to do
is tie the destination BACK to the original step. The IDE won't let me,
because it would create a circular precedence.

I have a theory about using the execute dts package, but that looks like a
brutal way to have to do this. For one, it defintely requires external state
persistence (recording the last step executed) and #2, it would require
considerable trickery to repoint the pkg execution task to either the DB or
the File System, depending on whether I'm in development mode, or running on
a server. During development, I'd want it to execute the same dtsx file that
I have in the IDE. During non-interactive mode, I'd want it to run "wherever"
the original exntry point was run from. I'd need to figure out how to
determine these properties and set them at runtime for the "chaining task".

Any ideas? Thank you!

On Oct 10, 6:27 pm, Todd Beaulieu
<ToddBeaul... (AT) discussions (DOT) microsoft.com> wrote:
Hi Todd,

I understand your issue is that you wish to create a package (or
series of packages) that tracks the last executed step (by writing to
a database?) and is able to resume on the next step should the process
be interrupted. Please correct me if I'm wrong.

It sounds like this functionality would be best served by a ForLoop
container in the Control Flow (configured to operate as a while loop -
i.e. only set the EvalExpression property) and then a script/SQL task
that determines which step needs to execute next and sets an
appropriate variable (or set of variables). This task could then have
the remaining tasks connected to it using conditional flow connectors
(right-click on the connector line, choose "Edit" and then change the
Evaluation Operation to "Expression").
Don't forget to meet your EvalExpression to stop the loop when your
last task has completed!

Although this solution should work, depending on the number of tasks
in your package it could end up being difficult to understand and
maintain for future users. Another solution which I have used in the
past is to use the Execute Package task. The mechanism would be the
same (i.e. ForLoop container, step deciding what to execute next), but
the actual work is divided into other packages. Instead of
conditional work flow connectors, instead you connect to a single
Execute Package task and set which package to execute at runtime.
This modular approach also makes it easier to change (or add) steps in
future.
Also, the SSIS UI struggles when trying to edit large packages -
mostly due to all the design-time validation that goes on in the
background.

Good luck!
J

You solved my problem! Thank you very much!

The For...Loop was to trick needed to "loop" back. The only thing not pretty
is that everything in the package has live inside the For...Loop. Maybe I
should look at it as a picture frame!

I'll keep in mind the issue about complexity. I'd hate to start breaking out
one package into multiple packages when they're all related and "internal" to
one task. It would be like breaking a DLL out into many DLLs -- one for each
Class being exposed.

"jhofmeyr (AT) googlemail (DOT) com" wrote: