sql server dts: DTS Transactions -- sql server dts

DTS Transactions

GayeF
6/29/2004 3:30:02 AM

sql server dts:

Hi,
I have a database with a number of temporary tables. The data in these temporary tables need to be transferred into tables in the same database. There are 5 transformations in all. I need to use transactions in the DTS job becasue if one transform task fails then I need to rollback all inserts to the tables. I am using Data Transform tasks because I need to use the error handling capabilities that come with it (i.e. source, dest error rows).
The 'Join Transaction if Present' and 'rollback on failure' options are set for each of the Transform Data Tasks and the last one has the 'commit on success' option set. I am using 2 connections for all 5 transformation tasks - the source connection is used for the temp tables and the dest connection for the 'real' tables. Both connections point to the same database. When I run the package the first transformation task executes correctly but the second returns the following error:

"Transaciton Context in Use By another session"

Have you any Ideas on what may be the problem?

Rgds

DTS Transactions

Pegasus
6/29/2004 6:48:32 AM

You could try limiting the maximum number of tasks
executed in parallel to 1 in your DTS Package Properties
(on General at bottom of page)

Hope this helps

Paula

[quoted text, click to view]
data in these temporary tables need to be transferred into
tables in the same database. There are 5 transformations
in all. I need to use transactions in the DTS job becasue
if one transform task fails then I need to rollback all
inserts to the tables. I am using Data Transform tasks
because I need to use the error handling capabilities that
come with it (i.e. source, dest error rows).
[quoted text, click to view]
failure' options are set for each of the Transform Data
Tasks and the last one has the 'commit on success' option
set. I am using 2 connections for all 5 transformation
tasks - the source connection is used for the temp tables
and the dest connection for the 'real' tables. Both
connections point to the same database. When I run the
package the first transformation task executes correctly
but the second returns the following error:
[quoted text, click to view]

Re: DTS Transactions

Allan Mitchell
6/30/2004 9:52:31 AM

That is because you are asking the same connection to participate multiple
times in the same transaction. Create seperate/distinct connection objects
for each connection you want to use.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
[quoted text, click to view]
temporary tables need to be transferred into tables in the same database.
There are 5 transformations in all. I need to use transactions in the DTS
job becasue if one transform task fails then I need to rollback all inserts
to the tables. I am using Data Transform tasks because I need to use the
error handling capabilities that come with it (i.e. source, dest error
rows).
[quoted text, click to view]
set for each of the Transform Data Tasks and the last one has the 'commit on
success' option set. I am using 2 connections for all 5 transformation
tasks - the source connection is used for the temp tables and the dest
connection for the 'real' tables. Both connections point to the same
database. When I run the package the first transformation task executes
correctly but the second returns the following error:
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.