General discussion

  • Creator
    Topic
  • #2178288

    Rollback a DTS-package

    Locked

    by jaak.stappers ·

    Hello
    We run several DTS-packages on an SQL server 2000 during the night.
    Each package updates several tables by first dropping them, then creating new instances and finally copying the data from the source into them.
    The problem is that the connection to the source is established after the drop and the creation. So, when this connection can’t be established, the new tables contain 0 records.
    Can somebody tell me how I can rollback the package or how I can test on the connection the source prior to the drop and creation and cancel the package if the connection fails.

All Comments

  • Author
    Replies
    • #3122488

      Well you need

      by tony hopkinson ·

      In reply to Rollback a DTS-package

      you need to wrap the drop, create an insert into one transaction.
      Start Transaction

      COMMIT
      or RollBack

      You might find it easier to rename the tables instead.
      Drop all OLD_COPY_… if they exist
      Rename existing to OLD_COPY_…
      If Create & populate
      Unsuccessful – Delete existing and rename back
      Bit old school, but solid and you have all the old data still available until teh next time the package executes, which is a bonus if you can afford the disk space

Viewing 0 reply threads