August 10, 2007 at 8:05 am
Given a DTS Package that includes multiple execute package tasks, how can all of the child packages be included within a single transaction so that if any child package fails, any actions taken by any of the other child package are rolled back ?
Here is a simple case:
Create two tables in some user database:
create table DTS_Success
(dbidsmallintnot null
,sidvarbinary(85)not null
, primary key (dbid)
)
create table DTS_Fail
(dbidsmallintnot null
,sidvarbinary(85)not null
, primary key (sid)
)
Create a package DTSSuccess that contains:
2 connections named Source and Target
Source is a SQL Server with a database of master
Target is a SQL Server with the database being where ever the tables were created.
A transform data task using the 2 connections:
Source SQL is
select dbid, sid from sysdatabases
Destination is table DTS_Success
Create a second package named DTSFail that is similar to the package DTSSuccess except that the destination table is DTS_Fail. Note that since the DTS_Fail table has a primary key on SID, and the system databases are always owned by the SID for "SA", this package will always fail.
Create a third package DTSDriver that contains:
One connections named Target that is the SQL Server with the database being where ever the tables were created.
A execute SQL Task that has this SQL:
delete from DTS_Success
delete from DTS_Fail
2 execute package tasks, one for package DTSSuccess and one for package DTSFail.
Now add the workflow so that both execute package tasks are dependent on the success of the execute SQL Task. The execute package tasks should not have any workflow between them so that they can run in parrallel.
How is this made a single transaction ?
I have structured stored files for all three packages, so send me a email if you want these files.
SQL = Scarcely Qualifies as a Language
August 10, 2007 at 2:54 pm
Found the configuration settings:
For the child packages:
Under "Package Properties" on the "Advanced" tab:
Checked on for "Use Transactions"
Checked off for "commit on successful package completion"
For each step under "Workflow Properties" on the "Options" tab:
Checked on for "Join transaction if present" and "Rollback transaction on failure"
Checked off for "Commit transaction on successful completion of this step"
For the parent package:
Under "Package Properties" on the "Advanced" tab:
Checked on for "Use Transactions"
Checked on for "commit on successful package completion" - this is checked off for the child packages. If checked off for the parent package, then when the parent package completes, a rollback occurs.
For each step, under "Workflow Properties" and then "Options" tab (this is the same as the child packages):
Checked on for "Join transaction if present" and "Rollback transaction on failure"
Checked off for "Commit transaction on successful completion of this step"
SQL = Scarcely Qualifies as a Language
August 11, 2007 at 6:59 am
So much for that idea !
Seems MSDTC has many bugs and some do not have any fixes.
BUG: Error 3910, "Transaction Context in Use by Another Session"
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply