Hi,
I have a DTS package (call it Parent) that calls another package (call
it Child).

Parent has a transaction running and I want Child to inherit that
transaction.

I know how to set this all up but I have 1 question. If the
AutoCommitTransaction property of Child is set to TRUE, will that commit
the whole inherited transaction or does it not have any affect?
I still want my inherited transaction to continue in Parent after
successful completion of Child, so I don't want Child to commit the
inherited transaction upon successful completion and then have Parent
automatically begin another transaction.

TIA

Regards
Jamie

FYI: In a normal 1-package system if AutoCommitTransaction is set to
TRUE then the current transaction will be committed upon successful
completion of the package.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Here is my test for your scenario

CREATE TABLE Del1(col1 int) --used in first package
INSERT Del1 Values(1)
CREATE TABLE Del2(col1 int) --used in second
INSERT Del2 VALUES(2)

Package1 will delete from Del1 --> Call Package2 ---> Delete from a
fictional table

Package 2 will delete from Del2


Data remains in both tables after the final task on Package1 fails.





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

Thanks Allan,
What was the value of AutoCommitTransaction of package 2 when you ran
this?

Regards
Jamie




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

For each task in Package1 (Join tran if present - Rollback on error)
Same for tasks in Package2




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

Sure, but the AutoCommit property is a property of the package, not a
task. What did you have that set to?



I am actually trying this myself as I write. I have a parent package
which calls 2 child packages with a single transaction running over the
whole lot. The first child package completes successfully but as soon as
a task in the second child package executes it fails with the error
"Transaction context in use by another session".

I can sort of understand this. IF (big if) the packages are executed
under different sessions then this error message would make sense. But
then that makes a whole mockery of being able to run transactions over
multiple child packages.

Any ideas???

Thanks for your assistance by the way Allan.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

-1 on both


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

Futher to your last

Transaction context in use by another session

I see this when you try to use a connection to do one thing whilst it
is being used to do womethingelse.

I.e.

In a datapump task using one of the connections as the connection for a
lookup.


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

Thanks Allan,
I've actually managed to find out more about this over the weekend - its
actually a bug in SQL Server according to an article (279857) in the
knowledge base.

Cheers
Jamie




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Cool.

I have only seen it when I tried to do > 1 thing in a transaction on
the same connection object.



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