locked
ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION RRS feed

  • Question

  • Hi,

    First of all, retainsameconnection is set to true :).

    I have foreach loop , in which all files from specified folder are processed and inserted into SQL tables.

    Everything is in loop

    1.task : Process data by Script task, and fill variables with INSERT SQL statements

    2.task: Execute SQL task BEGIN transaction

    3.task: Sequence container which containts all Execute SQL tasks with all necessary INSERT statemens.

    4.task: On sequence containter Success COMMIT TRANSACTION

    5.task: On sequence containter failure ROLLBACK TRANSACTION

    So if I damage input file (in value where is expected integer I put alphabetical character) and one Execute SQL task in Sequence container fails, then ROLLBACK is not possible from reason ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. In fact I dont understand why, because If I put 10 files into folder and 9 are OK and 1 is not, then COMMIT is executed 9times without problem, only ROLLBACK makes problems.

    I used such combination many times (not in loop) and have never problems with COMMIT and ROLLBACK.

    Thanks for any idea

    Monday, February 27, 2012 4:47 PM

Answers

  • Since you already use the Sequence Container.... SSIS has its own implementation for transactions that you should employ:

    http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-integration-services-ssis/

    Then the question why the ROLLBACK does not work becomes a moot point.


    Arthur My Blog

    • Proposed as answer by Eileen Zhao Wednesday, February 29, 2012 1:43 AM
    • Marked as answer by Eileen Zhao Monday, March 5, 2012 1:43 AM
    Monday, February 27, 2012 4:53 PM

All replies