How to Rollback Transaction SSIS without MSDTC?

Question

1st .)

I have a Sequence container. It has 4 different execute sql tasks and 4 different DFT where data is inserting into different tables . I want to implement transaction with or without MSDTC service on the package failure i.e., each and every data should be rollback on failure of any of the DFT or execute SQL task . How to implement it? when I am trying to implement with MSDTC service I get the "OLEDB Connection" error and without MSDTC the data is getting inserted only the last execute Sql task is getting rolled back . How to implement this on ssis 2017?

2nd.)

when I tried without MSDTC by setting the property of ServerConnection RetainSameConnection as TRUE and took two more execute sql task for begin transaction and commit. I faced a issue with the EVENT HANDLER i.e., I was not able to log error into different table. Either the Rollback is working or the Event Handler when tried to manipulate. Soon as the error occurred the control goes to the event handler and then Rollback every thing including task in event handler

3rd.)

The Sequence Container is used for parallel execution of tasks. So the particular task among the 4 getting failed only that particular task getting rolled back rest SQL task was inserting data into tables.

Thanks in Advance!! ;-)

Answer 1

One option I've used (without MSDTC) is to configure your OLEDB conection as RetainSameConnection=True (Via Properties Window)

Then Begin a Transaction Before your Sequence Container & Commit afterwards (all sharing the same OLEDB Connection.

Works quite well & pretty easy to implement.

Answer 2

According to my Scenario : I used a sequence container(which contains different DFT's and tasks),and have taken 3 more Execute sql task :

1st Begin Transaction T1(before the sequence container)

2nd Commit transaction T1(after the sequence container)

3rd Rollback transaction T1(after the sequence container) with precedence as failure i.e, only when the sequence container fails the Execute Sql task containing rollback operation executes.

Note : I tried to rollback this way but only the current execute sql task i.e., the nearest to it was getting rolled back rest of the data were inserted. So whats the solution? In that same execute sql task I have truncated the table where rows were getting inserted. So, when the sequence container fails the execute sql task will truncate all the data in the respective table. (Rollback transaction T1 go truncate table Table_name1 go truncate table table_name2 go truncate table table_name3)

*IMPORTANT :***To make the above operation work make sure in the connection manager properties **RetainSameConnection is set to True by default it's false.

Now, to log errors into user defined tables we make use of event handler.So the scenario was, when the sequence container gets failed everything gets rolled back including the table used in execute sql task in the event handler. So whats the solution?

When you are not using SSIS transaction properties, by default every task have the properties set to supported. The Execute sql task in the Event handler also has the same properties as Supported so it follows the same transaction.To make the event handler work properly change the connection of the Execute Sql Task i.e, take a different connection and set its TransactionProperty to NotSupported. Thus, it will not follow the same transaction and when any error occurs it will log the errors into the table.

Note : we are using sequence container for parallel execution of tasks.What if? the error occurs inside the sequence container in any of the task and task does not allow to sequence container to fail.In that case, connect all the task serially.Yes, that makes no sense of the sequence container.I found my solution to work that way.

Hope it helps all! ;-)