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

SSIS Rollback Transaction Expand / Collapse
Author
Message
Posted Thursday, October 21, 2010 1:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
Hi
How to Rollback Transaction in SSIS ?

I have created a table as
Create table Temp (ssn int not null PRIMARY KEY CLUSTERED (ssn),FName varchar(50),LName Varchar(50))

and i am having a text file with data

ssn ,FName,LName
1,TestA,TestAA
2,TestB,TestBB
3,TestC,TestCC
4,TestD,TestDD
5,TestE,TestEE
6,TestF,TestFF
1,TestG,TestGG

I used Source Flat File Source and for Destination OLE DB Destination
and run the pack.
All the 6 records are inserting into the table but i dont want to insert them if there is a Violation of PRIMARY KEY then it should rollback the transaction.

I tried Sequence Container where the datas are not rollback
How to rollback the transaction

Find the attached sample

Thanks
Parthi



Thanks
Parthi


  Post Attachments 
Sample.JPG (15 views, 14.51 KB)
Post #1008217
Posted Friday, October 22, 2010 2:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
Hi
Any one has any idea about how to proceed for the above.It is on urgent

Thanks
Parthi


Thanks
Parthi
Post #1009024
Posted Friday, October 22, 2010 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 8:24 AM
Points: 41, Visits: 390
We have some ETL jobs that feed our data warehouses. If you try to insert a duplicate primary key, the primary key constraint should cause the update to fail. Do you have a primary key on the destination table?

What we are currently working on is having the ETL update continue in the event of a duplicate primary key and write the offending row to an alert log / email notification. Our current ETL process fails once a duplicate primary key is encountered. When it is restarted it begins from the failed row.

Would you want this process to continue or stop once a duplicate is encountered? If you want it to stop, make sure a primary key is on the destination table.







Post #1009320
Posted Monday, October 25, 2010 5:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
fenwicc (10/22/2010)
We have some ETL jobs that feed our data warehouses. If you try to insert a duplicate primary key, the primary key constraint should cause the update to fail. Do you have a primary key on the destination table?



Hi

I think you did not see my post there i have created the table with pimary key only thats why i am getting error but i need to rollback whole transaction i am able to insert 6 records,7th record is duplicate in the example so the whole process sholud not insert.

Thanks
Parthi


Thanks
Parthi
Post #1009888
Posted Monday, October 25, 2010 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 17, 2013 8:24 AM
Points: 41, Visits: 390
OK, if you want the entire transaction to rollback, take a look at this. Make sure the MSDTC is running. Hope this helps.
Thanks
Chris
Post #1009970
Posted Tuesday, May 10, 2011 12:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 23, 2011 2:35 AM
Points: 6, Visits: 26
Hi

MSDTC service is running and I have kept the transaction option required however it gets hang when I run the package. with supported it runs fine. can you please help me on this.


Thanks & Rgds,

Avadhut Joshi
Post #1105863
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse