roll back transaction in DTS package?

Question

Post reply

roll back transaction in DTS package?

Betty Sun

SSCrazy

Points: 2442
More actions
December 1, 2005 at 11:58 am

#66848

Hi all,
I have a simple DTS package scheduled to run daily.
Step 1 flush data in a table
Step 2 import data from a .txt file to database table.
Sometimes when data is corrupted, the step 2 is failed and my table becomes empty.
What I am trying to ask is how to roll back transaction step 1 if step 2 is failed.
I want to keep old data there instead of having an empty table.

Betty

Viewing 8 posts - 1 through 7 (of 7 total)

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

Erich Brinker SSCrazy Points: 2973 More actions · Answer 1

I have some similar packages...

Can you try this:

importing the data from the .txt file to a holding table, check that table to be sure there are valid rows in the table, if test is good truncate or delete your old data, copy data from the holding table to the table, then cleanup or drop your holding table.

I put each step into a job that way if your job step to check the data in the holding table fails you never get to the step to flush your data.

Betty Sun SSCrazy Points: 2442 More actions · Answer 2

Hi Erich,

I don't know what is the easiest way to check if the rows in the table are valid(use script or DTS package?). If in the DTS package, there is way to check if a certain DTS package finish all the sql task(steps) successfully(import data to a temporary table etc.), then I can set up another DTS package to copy data from one table to another.

Betty

Erich Brinker SSCrazy Points: 2973 More actions · Answer 3

This is how I would do it...

1 job step to create temp or holding table, 1 job step to call dts package to import txt to holding table, 1 job step to test for 0 rows

In your job you would put syntax like(I am using example test of making sure table is not empty, you can use whatever test you want to make sure data is good) decare @i Select @i = count(*) from temp table, If @i = 0 begin print Error msg, Rollback , end

That way if your test fails, your step will fail the job, and there will be no harm to the data.

Betty Sun SSCrazy Points: 2442 More actions · Answer 4

Erich,

Thanks for the idea. I guess I could put those task in a store procedure and let the DTS execute that store procedure. In that store procedure, just like you did, I could check the temp table if @i>0 then I could flush the table and then copy the data from temp table to the table.

The second way I don't know: Is there a way to call DTS package in store procedure, or in a DTS package call another DTS package?

Betty

mdaniel SSCarpal Tunnel Points: 4616 More actions · Answer 5

In this way you can call a DTS from a stored procedure:

http://www.databasejournal.com/features/mssql/article.php/1459181

From a DTS, just insert the Execute Package Task and configure it to run your DTS.

Betty Sun SSCrazy Points: 2442 More actions · Answer 6

Hi mdaniel,

Good advice and resouce. I will take a look.

Betty

Erich Brinker SSCrazy Points: 2973 More actions · Answer 7

You can add a sql task and have it execute your stored procedure.