Post reply

Dynamic connections, transactions and RetainSameConnection

  • February 13, 2012 at 8:45 am

    #255536

    Hello,

    I am currently working on an SSIS package which will loop through a number of connections in turn, reading data from and writing data to each one.

    This is fine - I know how to make a dynamic connection string and use for each.

    The problem I have, however, is that I also need to use transactions. I have been unable to get MSDTC working, so I have decided to manage the transactions manually.

    This is also fine - I know how to do this and set RetainSameConnection = True to allow the transactions to work correctly.

    I have had each technique working perfectly - but not both at the same time.

    This is where we hit the snag - as soon as I set RetainSameConnection = True, the dynamic connection fails to be dynamic any more - it just connects to the first database and keeps that connection throughout.

    Does anybody have any ideas how I could overcome this problem please?

    Thanks,

    Rachel.

  • February 14, 2012 at 12:01 am

    #1446645

    Are the sources and destination SQL Server? You might want to consider writing it all in TSQL.

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

  • February 14, 2012 at 2:04 am

    #1446675

    All of the code is in T-SQL stored procedures - it's the cross-server work that I use SSIS for.

    All of the servers are SQL Server, and I initially thought I'd use linked servers to do it, however I've had trouble getting linked servers to work with windows authentication and I don't want to use SQL Server authentication for this.

    I have now got it working nicely using a sub-package to do the work on each pass through the loop.

    If you can suggest a better way, however, I'm all ears! 🙂

    Thanks,

    Rachel

  • February 14, 2012 at 5:16 am

    #1446775

    Rachel Byford (2/14/2012)

    I have now got it working nicely using a sub-package to do the work on each pass through the loop.

    If you can suggest a better way, however, I'm all ears! 🙂

    I think this is your best option without linked servers.

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

  • February 14, 2012 at 5:18 am

    #1446776

    Great - thank you for your help.

    Rachel.

  • April 29, 2016 at 6:59 am

    #1875553

    Can you please tell me how it has worked for you,I am also having the same scenario.

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

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