I have a common scenario:

I have several packages that I want executed sequentially, and if one
fails they should all be rolled back. So, I made a main executing
package that executes each package. Each of the sub packages is set to
"Join Transaction if Present" and to "Rollback Transaction on Failure"
(no commit on complete). The main package will commit on successful
package completion.

However, when I run the main package, the first package executes fine
but then progress hangs indefinitely on the second package. Just to be
sure that it wasn't due to a badly constructed subpackage, I removed
the first subpackage so that the second became the first to run and the
same happened again -- hang on executing the 3rd package.

If I enable "Commit transaction on successful completion of this step"
for the sub packages, each one runs fine. But, I lose the transaction
rollback that I need.

Any suggestions?

Dave

What is in the sub packages?
Do they join a transaction?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


<omgletran (AT) gmail (DOT) com> wrote

The subpackages are nearly all similar. The subpackages all call the
same subsubpackage that checks a value in the database using a Execute
Sql task. If it matches an intended value, it will execute an Execute
Sql task.

The only types of tasks contained in the subpackages, and the one
subsubpackages, are Execute Sql Tasks and ActiveX Script Tasks. All
the Execute Sql Tasks are set to join transactions, not to commit on
completion, and not to rollback on failure.