I am trying to use explisit transactions within SSIS. The database I am working with does not have the DTC enabled, so I cannot use SSIS' inbuilt transaction handling.

I have execute sql tasks to initialise, commit and rollback the transaction, however despite having set "RetainSameConnection" to true, I am still getting the below error:

[Execute SQL Task] Error: Executing the query "ROLLBACK;" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  • Do you just have those three tasks at the moment or do you have something between them that does some work? May 13 '16 at 8:52
  • I do have a data flow and a file task in a sequence container.
    – Neil P
    May 13 '16 at 9:08
  • And what is the end result then? Is the data flow committed or rolled back? May 13 '16 at 9:10
  • the dataflow is committed, when it should have been rolled back, the rollback fails, as per the error above.
    – Neil P
    May 13 '16 at 9:14
  • 1
    Can you show an example control flow and data flow topology that reproduces the issue? May 13 '16 at 11:17

It turns out, I had done something really stupid.

My SSIS package, as it turns out was perfect, I had named the transactions and set "RetainSameConnection" to true. Begin/commit/rollback commands were all specified correctly.

My problem was that one of the stored procedures called as part of the SSIS package had a unnamed transaction in it, causing SQL Server to commit everything once the commit in that stored procedure was called.

Resolution was to modify the stored procedure to name the transaction, so that SQL Server treats that transaction as a nested transaction (In theory, I would have thought the second "begin transaction" statement would do this, but for some reason this was not happening).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.