run the whole package as a transaction. 1

[Jeet2004]

In my pacakage i have an sql task where i truncate the table and on success(workflow) it reads a txt file and transfers the data to sql table (a regular dts task)
Now here is my problem the thing is if for some reason the dts transformation task for reading from txt file to sql table if fails the result is the table is already truncated and this results in empty table which i dont need.
The package is run so thta the table is always have some data and it overwrites the whole table .
So what i need is if the dts task fails i should have the table with old data rather than truncated that.
Can i do this? if yes how can i achieve this.
Tahnks for your help

 

[mrdenny]

You can't run a DTS package within a transaction as DTS runs outside of SQL. I'd recommend loading the data into a staging table then if the import works move the data into the production table. If not don't.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[Jeet2004]

thanks for the information mrdenny but How can i achieve this ?

I mean by staging i am assuming you mean temp table but then how to handle success or failure of the dts task and commit the truncation by sql task.
because the sql task should first truncate the table and then dts task should run.
Hope i am clear if not let me know and i ll tyr to put it with example.
Thanks.

 

[mrdenny]

The idea is that you dump the data to a tempoary table and load the data. If the data load into the temporary works without error then you truncate the production table and load the data into it.

Step 1 truncate temp table
Step 2 load data into temp table
if step 2 is success continue to step 3
Step 3 truncate production table
step 4 load data from temp table to production table



Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)

 

[Jeet2004]

thanks .
"You got star"

 

[mrdenny]

no problem and thanks.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

(Not quite so old any more.)