adding dtc to my pkg

• stan

  Ten Centuries

  Points: 1002

  More actions

  December 23, 2024 at 6:40 pm

  #4503744

  Hi below i show the control flow of my pkg.   I'd like to enlist any component that updates into dtc.  I havent used dtc in a long time.

  i tried reading what's posted at the links you see below but id really like to get to the finish line faster.

  my understanding is that all servers better have the dtc service running, including where the pkg runs.

  so am i just setting the transaction option on the pkg properties to "Required"?   and to "Supported" on each read and write component?  If i want to delay the overhead that dtc brings and am willing to add a component, can i start it up midway thru the pkg after i'm done with integrate step?   what i would do in that case is simply stage what "integrate" does not caring what happens to the stage table if something fails because a truncate to that table would be added at the beginning of the pkg anyway.

  the integrate , rollup  and audit steps each do inserts but may do updates/deletes in the future.   the integrate and rollup do a lookup of data from a second server  to integrate with what's selected from the first server.  I show integrate's df in the 2nd image.

  https://www.sqlservercentral.com/articles/ssis-distributed-transactions

  https://sqlstudies.com/2016/01/06/msdtc-requirements-for-ssis-transactions/

  https://sqldeep.com/how-to-use-transactions-in-ssis-sql-server-integration-services/