Issue with roll back

[SqlHunter]

Is there any way that we can roll back once the data-pumping is done in DTS.Eg:-We have 3 work flows and the first 2 succeded and third one failed so is there any way to roll back to the intial stage

 

[mrdenny]

No, the transaction at that point is complete and commited to the database.

You would have to write delete scripts to handle this manually.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SqlHunter]

can't we make the whole work flow into one transaction

 

[mrdenny]

To make the 3 work flows one transaction, you would have to make them one work flow. Each workflow is a seperate connection to the SQL Servers, and so can not be combined into a single transaction.

If you are loading from one database to another, you might want to look into another method than DTS, such as using a linked server and loading the data in through T/SQL. That way you could wrap the entire process into a single distributed transaction, which would give you the ability to roll back or commit as you wanted.

That or load the data from the 3 work flows into temporary tables, then check the tables, and if all the data is there process it, if not don't and fail the job step with raiserror.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[john76]

It is actually possible to have 3 separate workflows in a single transaction, but there are a few caveates(sp?).

First, all of the tasks must use the same connection. This also means that as only a single task may execute at a time, SQL Server may do a lot of switching between workflows during execution.

Second, the easiest way to make the transaction work correctly is to have the package fail on the first error, and NONE of the individual tasks must have the "Commit on successful completion of this step" box checked (in Workflow Properties -> Options).

There is a way to monitor the package in an ActiveX task if you can't have the whole package fail on the first step failure (error reporting or something of the like) but I won't go into details unless neccessary.

HTH,
John

 

[SqlHunter]

Hi John,
Iam quite insterested to know how to make the things working the way u were explaining.I saw the option in the package properties as well.Could u please explain

 

[SqlHunter]

Here when u select the option of

"The easiest way to make the transaction work correctly is to have the package fail on the first error, and NONE of the individual tasks must have the "Commit on successful completion of this step" box checked (in Workflow Properties -> Options)".

It only works for that particluar step only right.one more thing is when u have job sheduled will it by pass when a new entry comes in or still go with the package error failure

 

[john76]

SQLHunter,

Sorry, been away for a while.

It only works for that particluar step only right.one more thing is when u have job sheduled will it by pass when a new entry comes in or still go with the package error failure

I am not sure what you mean here, can you explain further?

Since you seem to be interested in all three paths succeeding together or not at all, I'll try to explain my original post a little more clearly.

To have multiple "paths" in a single transaction:

1. Make sure that you only have 1 connection to SQL Server, as all tasks must join this single transaction to work correctly

2. Make sure that none of the individual tasks (such as an Execute SQL task or Transform Data task) has the "Commit on successful completion of this step" checkbox in "Workflow Properties -> Options" checked.

3. Have each task participate in the transaction by checking the "Join transaction if present" checkbox in "Workflow Properties -> Options"

4. In "Package Properties -> Logging" check the "Fail package on first error" checkbox

If you follow these steps, you should get an "all or nothing" package in which all of the steps complete successfully or the entire package is rolled back.

I have personally noticed some bugs in this form of transaction control. If you have all "Execute SQL" type of tasks you might be better off by explicitly declaring your transactions by adding more "Execute SQL" tasks at the start and end of the paths with "Begin/Commit/Rollback Transaction" statements to implement transaction control. However, I don't believe you can do this when using non-SQL tasks (such as Data-Driven Query or Transform Data tasks).

Hope this helps,
John