I have created a DTS Package that does the following:
1 Delete all data from table 1 (SQL Task)
2 Import Data from .csv file into table 1 (Data import)
3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)
This all works fine, but now i want a rollback function in step 1 and 2.
So when Step 1 is finished and something goes wrong i want the deleted data back.
what I would probably do in this case is add another column for table1 which "marks" a record for deletion.
the steps would then become....
step 1 mark existing records in table 1 for deletion
step 2 import new records
step 3 transfer records
- if there are errors
step 4 remove transfered records
step 5 delete records inserted in step 2
step 6 update records and set the mark for deletion column to blank