Hello,
I have a multi-level package hierarchy and I'm worried
about failures during scheduled dtsrun.exe executions.

If a step in a package far into my hierarchy fails then
what do I do? I don't want to have to run the whole thing
again as normal as then all the steps that previously
worked will get run again - and I don't want that.

I see 2 options for getting around this.

1) Execute the whole package hierarchy under a single
transaction. If a task fails, rollback the whole package
hierarchy. This doesn't sound like a @good-practise@
method to me.
2) Have some way that a task checks to see if it should
run, dependant on the progress of the whole package
hierarchy (i.e. If I ran successfully last time but the
whole thing didn't complete successfully, then don't run
this time.
How on earth do i do this though? Its something to do with
workflow ActiveX scripts I guess but it seems like a huge
task to try and accomplish.
Is there a tried and tested method for doing what I want
to do? I can't believe no-one has come across this problem
before. Some code snippets would be fantastic.

Thanks in advance.

Regards
Jamie

In article <OltOg3kcDHA.2508 (AT) tk2msftngp13 (DOT) phx.gbl>, JFB
<jfb2002 (AT) directvinternet (DOT) com> writes
<snip>
Aren't the Related Links on every page? Damn well should be, and I only
just wrote that bit of code

Darren

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

How about my idea of putting the whole thing into a single
transaction (Using "Join Transaction" in the workflow
properties). Does anyone think this is a goer? The only
problem I can see with it is that I don't think an Execute
SQL Task (of which I have a few) can join a transaction.

Thanks
Jamie

Exec SQL Tasks can enlist in a transaction provided the connection type
supports distributed transactions.

To get a transaction to span multiple packages you will need to use the Exec
Pkg Task to call the child packages.

Transactions are useful, but nor always practical. Requirements such as name
resolution and the ports needed often make them unsuitable for WAN links and
similar.


--
Darren Green
http://www.sqldts.com

"Jamie Thomson" <jamie.thomson (AT) int21 (DOT) com> wrote