Search code examples
ssissql-server-2012sql-server-data-toolsssis-2012sqltransaction

SSIS - Sql transaction rollback throwing error

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?

Solution

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