Post reply

SSIS Rollback Transaction

  • October 21, 2010 at 1:55 am

    #225300

    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

  • October 22, 2010 at 2:53 am

    #1240772

    Hi

    Any one has any idea about how to proceed for the above.It is on urgent

    Thanks

    Parthi

    Thanks
    Parthi

  • October 22, 2010 at 9:52 am

    #1241026

    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.

  • October 25, 2010 at 5:08 am

    #1241488

    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

  • October 25, 2010 at 7:21 am

    #1241557

    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

  • May 10, 2011 at 12:06 am

    #1322096

    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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply