0

I have created an SSIS 2012 package with a for loop container planned to run continuously (infinite loop) so that it can process an incoming Excel file as soon as it is dropped into a designated drop directory. The processing involves first clearing all the existing records in the target SQL Server 2012 database table and then bulk inserting the data from the Excel into the table. So obviously it needs to maintain transaction, so that the data deletion operation can be rolled back in case the bulk insert fails (due to invalid file data, etc).

Below are the tasks in the for loop container :

  1. Script Task which leverages the C# FileSystemWatcher to watch for an Excel file dropped to the drop folder.

  2. Execute SQL Task with the below statement :

Begin Transaction tran1;

  1. Data Flow Task with Excel Source and OLEDB destination to insert the data from Excel to the SQL Server database table. This task has an OnError error handler which uses an Execute SQL Task to log the error details to a user defined error log table.

  2. On success precedence constraint of the above, an Execute SQL Task with the below statement :

Commit Transaction tran1;

  1. On failure precedence constraint of #3 above, an Execute SQL Task with the below statement :

Rollback Transaction tran1;

Note that I cannot use the default DTC based transaction as MSDTC is not enabled in the database server. Hence using the native SQL transaction. Also, I am using a single OLEDB connection manager in all DB related tasks (it is a remote Sql Server database, hence using OLE DB). The RetainSameConnection property of the Oledb Connection Manager is set to True. Also, all DB related tasks have their transaction option set to Supported. However, on running the package with an incoming Excel file with invalid data, the task #5 for rollback throws following error and the same is logged into the error log table :

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I should add that, for a valid Excel file, the package runs fine with the Commit Transaction (task #4) completing without error. Any idea what might cause the transaction rollback to throw the error?

Tanmoy Sengupta
  • 406
  • 4
  • 18
  • I doubt that the transaction would work like this in SSIS. Beginning the transaction scope is one task and either trying to commit/rollback in a different one. – Rex Aug 24 '17 at 20:07
  • That's why the RetainSameConnection property of the connection manager is set to true and all the tasks uses the same connection manager. – Tanmoy Sengupta Aug 24 '17 at 20:11
  • Also, I initially created the package without the for loop container and without OnError error handler for the DFT, and both commit transaction and rollback transaction tasks ran successfully that time. So begin transaction in one task and commit/rollback in another definitely works as long as RetainSameConnection is true. – Tanmoy Sengupta Aug 24 '17 at 20:26
  • I just created a sample package with your steps and it worked for me. – Rex Aug 24 '17 at 20:32
  • Will it be possible to share the solution so that I can look? – Tanmoy Sengupta Aug 24 '17 at 20:34
  • How do I share solution? – Rex Aug 24 '17 at 20:39
  • May be via Google drive? – Tanmoy Sengupta Aug 24 '17 at 20:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/152793/discussion-between-rex-and-fuzzygeek). – Rex Aug 24 '17 at 20:47

1 Answers1

0

I was able to solve this problem by changing the failure precedence constraint from DFT to Rollback EST from Logical AND to Logical OR.

Tanmoy Sengupta
  • 406
  • 4
  • 18