<ToddBeaul...@discussions.microsoft.com> wrote:
> 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!

"jhofmeyr@googlemail.com" wrote:

> 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
>