Integration Services Roll back on Failure

Posted on 2007-07-26
Medium Priority
Last Modified: 2013-11-30
I am new to integration services (sql server 2005) and am trying to set up a DTS package (SSIS Package).

In old sql, I would make a package.   As part of the package I would make it a transaction and roll back on failure.

I have a oracle database and a SQL database.  I want to import from oracle.table1 into sql.table1, replacing the rows in sql.table1 with those from oracle.  Using the wizard, it provides a turncate statement followed by a dataflow of import.

However, I need it to roll back to pre-turncate if the import fails.  What step am i missing?
Question by:kevinscheidt
  • 3
LVL 12

Accepted Solution

patrikt earned 2000 total points
ID: 19575769
As TRUNCATE TABLE is nonlogged you have to replace it by DELETE. Than you could setup transaction arround it and rollback in case of error.
LVL 12

Assisted Solution

patrikt earned 2000 total points
ID: 19575828

Author Comment

ID: 19575929
In the preparation step, I set it to "DELETE FROM TABLE X" instead of truncate.

I put a Sequence Container around the Control flow and set it to Transaction Required.
Thus, does this now mean that if the DataFlow Task fails, then the Transaction will roll back and undo the Preparation SQL task?
LVL 12

Expert Comment

ID: 19580692
Yes, from what you wrtite it looks right. But please do the test example first before going to destroy production data :) :)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question