Search code examples
sql-serverssistransactionsssis-2012ssis-2008

ssis transaction with out Msdtc

One of the packages is going to implement using SQL Server Integration Services SSIS Transactions without MSDTC.

The Execute SQL task has placed ,before the data flow(Df_insert) for begin transaction.There are several update steps and index creation steps ,after this First data flow(Df_Insert).There is an update scripts which is in another sequence container and ,need to be part of this transaction.

Is there any way to include only the Df_insert and the update scripts in the transaction.

The control flow looks like, the below

enter image description here

Solution

  • From SQL Transaction point of view ALL DML statements, i.e. inserts-updates-deletes, between BEGIN TRAN and COMMIT are part of this transaction and not deducible. Your task - committing only DFT and update script - means that update, update2 and delete are temp data used in your update script and discarded later on.
    Approach - rework your logic to move results of update, update2 and possibly delete results into TEMP tables and use it afterwards. Regular #temp_table will be fine since you have to use RetainSameConnection=true for transaction without MSDTC.