none
SSIS Sequence Container TransactionOption RRS feed

  • Question

  • I have an SSIS Package with two Sequence Containers.

    Sequence Container 1 has the TransactionOption Property set to "Required" and Sequence Container 1 (which is chained off Sequence Container 1) has the TransactionOption Property set to "Supported".

    If something fails in Sequence Container 1 the transaction is rolled back.

    If something fails in Sequence Container 2 the transaction is NOT rolled back???

    All tasks inside both containers also have Transaction Option set to  "Supported".

    I then tried to put all tasks inside one container and it works.

    I have no idea what is wrong here????


    CG

    Tuesday, September 9, 2014 11:23 AM

Answers

  • Thats because Sequence Container 2 is not part of any existing transaction. Having a transaction option as Supported means it will become part of a transaction only if one exists at parent level which in this case is package itself. So for it to rollback changes in case of any failures there should be a transaction atleast at the package level ie TransactionOption for package should be required. I think in your case its not so as you've enabled transaction only for sequence container 1 which is beyond its scope.

    So if you want any failure inside package ie inside sequence container 1 or 2 causing all things to be rolled back then you need to make transaction option as required for package or add both these sequence containers within another sequence container and enabled transaction for it by setting TransactionOption property to required.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by NuTech Wednesday, September 10, 2014 4:29 PM
    Tuesday, September 9, 2014 11:32 AM
  • Thats because the previous transaction which is populating the table has not been over yet. So it places locks on the tables and data flow task waits for lock to be released which is why it never gets executed. I think what you can try is to make TransactionOption as required for container which populates this table and then I think it should be able to read the contents.

    Alternatively you can use isolation level of READ COMMITED SNAPSHOT inside query and then it will be able to read previously commited data from table. But keep in mind that any data which is populated inside recent transaction will not be available..

    Make sure you read this

    http://visakhm.blogspot.in/2010/02/avoiding-deadlocks-using-new.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Wednesday, September 10, 2014 5:12 PM

All replies

  • Thats because Sequence Container 2 is not part of any existing transaction. Having a transaction option as Supported means it will become part of a transaction only if one exists at parent level which in this case is package itself. So for it to rollback changes in case of any failures there should be a transaction atleast at the package level ie TransactionOption for package should be required. I think in your case its not so as you've enabled transaction only for sequence container 1 which is beyond its scope.

    So if you want any failure inside package ie inside sequence container 1 or 2 causing all things to be rolled back then you need to make transaction option as required for package or add both these sequence containers within another sequence container and enabled transaction for it by setting TransactionOption property to required.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by NuTech Wednesday, September 10, 2014 4:29 PM
    Tuesday, September 9, 2014 11:32 AM
  • Thanks for the quick reply - that resolved my problem.

    I now have three Sequence Containers; One Main Container(TransactionOption=Required) and three Containers(TransactionOption=Supported) within this main Container.

    In my last nested container I have a Data Flow Task which queries a table populated in a previous task in a previous container. The Data Flow Task never completes and I get no error; when I drill down on the Data Flow Task I can see that nothing is ran. However, when I execute this Data Flow Task on it's own it works fine (as opposed to executing the entire Package).

    If I also set TransactionOption=NotSupported for all containers everything runs fine.

    Any idea as to why this is?

    It would appear that there is a lock on the table?


    CG


    • Edited by NuTech Wednesday, September 10, 2014 4:40 PM Update
    Wednesday, September 10, 2014 4:33 PM
  • Thats because the previous transaction which is populating the table has not been over yet. So it places locks on the tables and data flow task waits for lock to be released which is why it never gets executed. I think what you can try is to make TransactionOption as required for container which populates this table and then I think it should be able to read the contents.

    Alternatively you can use isolation level of READ COMMITED SNAPSHOT inside query and then it will be able to read previously commited data from table. But keep in mind that any data which is populated inside recent transaction will not be available..

    Make sure you read this

    http://visakhm.blogspot.in/2010/02/avoiding-deadlocks-using-new.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Wednesday, September 10, 2014 5:12 PM