Rolling back a package

• marka

  SSC Rookie

  Points: 29

  More actions

  January 20, 2003 at 5:00 am

  #105612

  I have a very simple DTS package that extracts data from a spreadsheet and then "on success" it executes a simple stored procedure using 'Execute SQL Task'.

  I noticed that if the 2nd step failed (the SP) the first step still completed and copied the data from the spreadsheet into the database. Of course this meant that the data was not complete in the table because the stored procedure manipulated it.

  How do I ensure that the entire package completes or all steps rollback?

  Thanks

• AnzioBake

  SSCarpal Tunnel

  Points: 4134

  More actions

  January 20, 2003 at 5:27 am

  #446742

  In DTS Designer go to the package properties, i.e.

  Click package,....properties.

  Click advanced tab

  Also check the workflow properties of the step . You need to check the options tab.

  I need to check in the book i recently finished on DTS but I have was in an accident since si this is a bit fuzzy right now.

• philcart

  SSC-Forever

  Points: 47794

  More actions

  January 20, 2003 at 2:57 pm

  #446808

  You could also use transactions within your stored procedure. That way any changes it makes will be rolled back on failure.

  Thanks

  Phill Carter

  --------------------
  Colt 45 - the original point and click interface