0

I have an SSIS Package Which Updates a Table in a machine and then needs to copy the data to a different machine(server).

The Package Often Fails Due to Time out failure,So we've come up with an option of first loading it to a temp table and then load from temp to main...So that the main table data doesn't get affected just in case the Connection fails...

Now we want to re-start the Package just in case it fails..and we've been using checkpoints.. and Retry options while scheduling the job.

The job works good but it has a problem..The temp table has some data just in case it fails...Now I want the Package to Rollback the data using an execute SQL Task before I run it again from it's failure Point.

How do I direct the Execute SQL Task to failure status after it succeds(in performing RollBack)...and make sure the package starts from the block in container where it failed.

Attaching the flow path... enter image description here

VJAI
  • 111
  • 1
  • 14
  • It sounds like you need a TRY..CATCH in your execute sql task. Then use rollback tran and RAISERROR in the CATCH block. Does that make sense? Keep in mind that your temp table data will be lost when the package ends execution. – Mark Wojciechowicz Oct 22 '14 at 12:17
  • Checkpoints are not your friend. You will need to put that logic into your package. – billinkc Oct 22 '14 at 15:00

1 Answers1

0

Go for additional table storing metadata, have queries for deletion of each task tasks. In this way you would just need to run the query in metadata before execution of task. Following this way you can also maintain the logs as well as define auto retry.

Rangani
  • 206
  • 3
  • 6