none
transaction across multiple databases

    Question

  • I have the need to move data from 3 different databases on one server, to the equivalent 3 databases on a different server.

    After experimenting with starting a transaction with a sql task, I think I am going to need to need to use the DTC after all.  So Im coming back to this example here

    http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

    With using sql task(s) to do the begin tran/commit/rollback, each task is associated with a single connection manager.

    That's fine, but I have several databases that I need to move from/to.

    With a given connection manager, you can use "retainsameconnection = true", but again, this only helps if you really cant use the DTC and you only have 1 database your dealing with.

    So when using the DTC, does the service have to be running on both source and destination servers, as well as where Im running the package from?  In my case, Im running from my local machine and moving data from server A (3 databases) to server B (3 databases).  fyi - have 3 sets of dataflow tasks that use separate connection managers.



    • Edited by shiftbit Wednesday, June 26, 2013 5:57 PM
    Wednesday, June 26, 2013 5:56 PM

Answers

  • Well, after trying it a few times and getting "The transaction manager has disabled its support for remote/network transactions" errors, I found other threads that mentioned enabling network access for the DTC.  Turns out that I just needed to enable network access for my local DTC service, as the remote servers already had it enabled.

    Wednesday, June 26, 2013 6:41 PM