SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Rollback Transaction


SSIS Rollback Transaction

Author
Message
parthi-1705
parthi-1705
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1964 Visits: 2196
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
Attachments
Sample.JPG (21 views, 14.00 KB)
parthi-1705
parthi-1705
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1964 Visits: 2196
Hi
Any one has any idea about how to proceed for the above.It is on urgent

Thanks
Parthi

Thanks
Parthi
fenwicc
fenwicc
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 406
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.
parthi-1705
parthi-1705
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1964 Visits: 2196
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
fenwicc
fenwicc
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 406
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
avadhut.joshi 17339
avadhut.joshi 17339
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search