Rollback Entire DTS Package

Question

Rollback Entire DTS Package

tygun

SSCrazy

Points: 2381
More actions
November 12, 2002 at 9:13 am

#105349

I have a DTS Package that truncates 2 tables and then imports data from 2 separate tables into the truncated tables.
My problem, if either import fails I want both tables to go back to the way they were prior to being trucated. Now I thought this could be controlled within the DTS Package Properties, but it doesn't seem to be working.
Any help would be great.
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Mkumari Hall of Fame Points: 3498 More actions · Answer 1

I have a suggestion,Create similar kind of tables (intermediate) import the data into these tables,once the import is completed drop the old tables and rename the intermediate tables.

tygun SSCrazy Points: 2381 More actions · Answer 2

I agree that this would solve my problem, but down the road it will take more time and resources to do all that. I know there is a way to control this I'm just not sure how.

Thanks anyway.

quote:
I have a suggestion,Create similar kind of tables (intermediate) import the data into these tables,once the import is completed drop the old tables and rename the intermediate tables.

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 3

You could use a stored procedure. In the stored procedure make all the SQL statements (Truncate & Import ) part of a transaction. See if that works.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Truncate is immediate and non-recoverable as is not logged. Even as part of a transaction you cannot get the truncated rows back. You could instead use DELETE but you have to do the delete and import within the same step in the package to make part of a single transaction. Mkumari has the only available solution with a TRUNCATE.

tygun SSCrazy Points: 2381 More actions · Answer 5

Thanks guys. I guess I'll go with door #1.

🙂

quote:
Truncate is immediate and non-recoverable as is not logged. Even as part of a transaction you cannot get the truncated rows back. You could instead use DELETE but you have to do the delete and import within the same step in the package to make part of a single transaction. Mkumari has the only available solution with a TRUNCATE.

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 6

Pls explain this.

I had a truncate and an insert statement enclosed in a transaction.

QUOTE

BEGIN TRANSACTION

truncate table mastersmi2

insert into mastersmi2

select top 10 * from mastersmi

ROLLBACK TRANSACTION

UNQUOTE.

I was able to retrieve all the records. This table has 300,000 records. The end result was 300,000 records. By your theory the end result should have been 0 records.

Also let me know if I am doing anything wrong.

Thanks

Edited by - vstitte on 11/14/2002 3:06:00 PM

Antares686 SSC Guru Points: 125444 More actions · Answer 7

That would be my understanding and I what happened when I tested but you did an INSERT so I would assume 10.

What version of SQL are you running? If 2K do you have FULL recovery model tunred on? I haven't tried that one. I will try to test again tomorrow to be 100% sure.

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 8

It is SQL2k and the recovery mode is set to Simple.

Thanks...

Antares686 SSC Guru Points: 125444 More actions · Answer 9

quote:
It is SQL2k and the recovery mode is set to Simple.
Thanks...

I stand corrected, I just tried it and works with ROLLBACK, makes no sense based on documentation unless I missed something as TRUNCATE is a bulk unlogged transaction and you are not supposed to be able to roll those back as there is no logging of that. However, I test under SQL 200 SP2 and SQL 7 SP4 and that is good enough for me. I do suggest you always test something, you may find you are wrong in your assumption. Thanks vstitte for pointing this out.

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 10

I tested it on SQL2k SP4. The recovery mode is set to Simple.

The Rollback works and retrieves all the records.

You probably need to test again please.

Try the SQL that I pasted and change the table name to a table that you have.

Thanks..

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 11

one more thing. I believe the Explicit transaction should take precedence over the TRUNCATE statement.

If you issue the Truncate statment on its own you won't be able to retrieve the records. But if you enclose in an explicit transaction then you should be able to return to the original state.

Thanks...

Antares686 SSC Guru Points: 125444 More actions · Answer 12

That is probably the answer, I will check on this later.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 13

Be sure you have the latest SQL Server service pack. DTS is constantly undergoing change due to numerous bugs. As you noted, you control this on the 'advanced' tab for the properties for the package. check "use transactions" and "commit on package complete" and you should be ok.

per some note, TRUNCATE is a "minimally" logged operation and can successfully be rolled back inside a transaction.

TomBrattin SSCrazy Points: 2436 More actions · Answer 14

The reason that you can roll back a truncate is because the actual data changes made during a truncate are logged.

The difference between 'truncate' and 'delete from' is that delete from deletes rows from the table one row at a time (logging each delete), while truncate simply zeros out the pointers on all root pages. The updating of the root pages is a logged operation, so it can be reversed.

Note: 'root pages' is my terminology, not Microsoft's, I forget what they call them.