Post reply

Transaction across multiple databases in SSIS

  • June 5, 2012 at 9:31 pm

    #1497380

    The package fails when two SQL tasks run simultaneously

    Package => TransactionOption=Required

    Execute SQL Task => TransactionOption=Supported

  • June 7, 2012 at 4:43 am

    #1498063

    I don't believe you will be able to run the two SQL Tasks simultaneously if they are transacted.

    I have received exactly the same error when attempting to run two SC's in parallel when they are transacted so it is possible you have the same issue here.

    Try and run the SQL Tasks in sequence and I think you may find that works.

    I have a lot of notes on this and similar behaviour and it is all to do with the way that MSDTC works. It becomes quite complicated when you start to look into it !

    Paul R Williams.

  • June 7, 2012 at 4:46 am

    #1498065

    Thanks Paul for suggesting the solution.

    As the requirement is to run the sql tasks in parallel. So is there any way to run the tasks in parallel (within transaction)?

  • June 7, 2012 at 5:10 am

    #1498068

    Not as far as I am aware.

    You state the requirement is to run the tasks in parallel, but why ?

    If there is a lot of data processing then running them in parallel will speed things up but if not then the difference will be insignificant.

    I would suggest running them in parallel (not transacted) and in sequence and compare the difference in completion times. If they are not significant is there still a requirement to run in parallel ?

    Paul R Williams.

  • June 7, 2012 at 5:13 am

    #1498069

    Yes Paul. We need to process huge data. Thats why we want them to be run in parallel but within a transaction. Running them sequenctially will take a lots of time.

  • June 7, 2012 at 5:15 am

    #1498070

    Tarun Jaggi (6/7/2012)

    Yes Paul. We need to process huge data. Thats why we want them to be run in parallel but within a transaction. Running them sequenctially will take a lots of time.

    What is huge data? Remember that by running things in parallel you can also create an I/O bottleneck, so it's not always 100% performance gain.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 7, 2012 at 6:17 pm

    #1498460

    This is an option you may want to look into: http://sqlparallelboost.codeplex.com/

    I don't know much about it myself though.

  • June 11, 2012 at 4:05 am

    #1499218

    I also tried adding a Execute SQL Tasks with query "BEGIN DISTRIBUTED TRANSACTION" with RetainSamConnection for that connection manager is TRUE to support "Transaction across multiple databases" but that is also not working.

    Is it possible to execute "BEGIN DISTRIBUTED TRANSACTION" in SSIS to support this?

Viewing 8 posts - 16 through 22 (of 22 total)

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