0

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

user1254579
  • 3,901
  • 21
  • 65
  • 104

1 Answers1

1

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.

Ferdipux
  • 5,116
  • 1
  • 19
  • 33
  • Thanks .update, update2 and delete are not temp data used in the script.It does update a source table and delete somehing from another table.Just wondering is ther any otehr way to implement this with out move result of update ,update2 and delete !? – user1254579 Jun 08 '16 at 15:39
  • 1
    Just other way around. You created/modified some data source and other table in update, update2 and delete statements (delete creates _minus_ data). This data is used somehow used in your SCRIPT. You can offload this data somewhere and discard it later on. Alternative could be storing data from your source into #Temp table with SELECT INTO #Temp, performing required data manipulations and doing your SCRIPT. Anyway, it is hard to tell without knowledge of your data manipulations. – Ferdipux Jun 08 '16 at 15:54