Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rollback in SSIS Expand / Collapse
Author
Message
Posted Thursday, May 21, 2009 2:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 25, 2010 5:54 AM
Points: 2, Visits: 114
What I want to achieve is to have a Rollback on the inserts when any one of the tasks fail in the container (By a method other than using Event Handlers).

I have created a package with a container containing two tasks.
Task1: Execute SQL Task-> Here I’m truncating a table which I want to load.
Task 2: Dataflow Task->Here I am loading the data.

I want that if Task 2 fails then there should be no truncation in its previous task.

I have used:
i) Transaction option as “Required”
ii) Started the MSDTC

Any help on this will be much appreciated.

Cheers
Post #721170
Posted Thursday, May 21, 2009 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 9, 2009 12:16 PM
Points: 1, Visits: 25
Sequence Container will help your problem. Sequence container commits the transaction if it completes successfully, an it rolls backs the transaction if it fails any one of the execution

In Sequence Container simply configuring the TransactionOption property to have the value REQUIRED.





Post #721314
Posted Thursday, May 21, 2009 9:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 25, 2010 5:54 AM
Points: 2, Visits: 114
Thanks for the reply sudhir!
Well thats what I was doing exactly but because I was initially Truncating the tables instead of Delete, I was not able to rollback.

Got it sorted !

Sometime sanity gets to its bare minimum

Cheers
Post #721822
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse