Post reply

Transaction handling in SSIS

  • June 5, 2009 at 6:54 am

    #132725

    I have 4 Data Flow Tasks in my package. First DFT has a no. of OLE DB Source and Destinations.

    Rest all DFT have only one Src and Des. All the OLE DB Source are using one connection and Destination are using other connection.

    First DFT copies data of Master tables from one Database to other database without any condition.

    Rest all DFT copy data of Transaction tables from one Database to other database based on some condition.

    I want if package fails anywhere in between, it should rollback all the tasks. That is either the data is copied for all tables or none of the tables.How can I achieve this ?

  • June 5, 2009 at 8:28 am

    #1005314

    I think you would want all 4 DataFlow tasks within a sequence container.

    Set Sequence Container property 'Transaction Option' to 'Required'. This forces the sequence to initiate a transaction.

    For good measure i think you set Transaction Option to 'supported' for all the data flows withihn the sequence...causing them to join the transaction.

    see here:

    http://www.databasejournal.com/features/mssql/article.php/3606681/SQL-Server-2005-Integration-Services---Transaction-Support---Part-25.htm

  • June 20, 2012 at 11:51 pm

    #1503765

    Hi All,

    I tried the solution which you had provided before still i am not able to rollback the previous success DFTs .

    I had taken 2 DFTS and inserted in the sequence contianer and set the Transaction Property of the Required option.

    And the 2 dfts which i had inserted are set to the option supported.One of my task is getting succesed and other is getting failed but the transactrions not getting rolled back as the DFt which is successful is getting commited.

    Can anyone Please provide the solution for these...

    Thanks in advance

Viewing 3 posts - 1 through 2 (of 2 total)

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