Hello,
I need some information about transactions across multiple
packages.

Here's a hypothetical situation.

I have a package (packageA) containing 2 tasks, both
are "Execute Package" tasks. The first task executes a
package called subpackageB and the second task (which only
executes on successful completion of the first task)
executes a package called subpackageC.

I run packageA. Suppose subpackageB executes successfully
but subpackageC fails for whatever reason. I want to
rollback all of the changes made by packageA INCLUDING all
of the changes made by subpackageB. Is this possible? I
think it is but I'm getting lost in the documentation so
just though I'd come on here and ask the gurus.

Lets multiply the problem a bit. Suppose I have a package
that calls 3 sub-packages. Each of those 3 sub-packages
calls 4 further sub-packages. Hence I have a package
hierarchy with 12 packages at the leaf level. Can I
configure everything so that if the very last package at
the leaf level fails, every change made by all of the
other leaf level packages are rolled back also?

Thanks in advance for your help. I'm imagining that this
is the sort of problem everyone encounters at some point
so I'm hoping there's an easy answer.

Regards
Jamie

OK

Here is what I did

p0 - Master package
p1 - Datapump between SQL Servers
p2 - Script task that returns failure

Function Main()
Main = DTSTaskExecResult_Failure
End Function


They all must be capable of supporting distributed transactions (SQL
Server --> Text file will fail)
p0 and p1 must use transactions in the workflow of each task and be set to
rollback on failure

Works for me





--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



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

Grand, thanks Allan

And my other hypothetical situation reharding a large
hierarchy of packages? Would it still roll everything back
then? I'm assuming it would cos its basically the same
problem on a larger scale - I just want to know if there's
any "gotcha's" that I haven't thought of waiting for me
when i try and do it..

Thanks again.

Regards
Jamie

I would go along with your hypothosis. I haven't tested it but as long as
you know the rules you should be OK.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



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