none
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. RRS feed

  • Question

  • Hello everyone, 

    i am facing some trouble with my transaction in SSIS package. 

    I have a Begin Transaction task (SQL) after which i start my Data flow task to insert the data from csv file to a staging table.

    Next, i have an SQL task (SP to insert data from Staging table to PROD table). 

    Now if the data flow task fails, SSIS correctly goes to the 'Rollback Transaction' task. However, if the SP fails SSIS throws the following error...

    "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION."

    Please NOTE that the Property 'Retain Same Connection' is set to TRUE for the OLEDB Connection. Please see below a screenshot of my Package. Any help would be highly appreciated. Thanks. 


    Thursday, December 12, 2019 1:55 PM

Answers

  • Hi Muhammad,

    'Begin transaction' and 'Rollback' are not dividing independent component. Once you have closed the connection, all your uncommitted transaction get rollbacked, if not explicit committed(Started with begin transaction statement).

    Easy way to rollback a transaction

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by M. Ali Shah Tuesday, December 17, 2019 10:17 AM
    Monday, December 16, 2019 11:21 AM

All replies

  • Hi M. Ali Shah,

    In my test, things go well when 'Insert data into table' performs a success:

    And it rollback when 'Insert data into table' failed:

    May I ask that if your TransactionOption is set to Required? And configure all tasks within the Container to Supported, they will all join the transaction started in Container, and if any fail, the transaction will be rolled back.

    Best Regards,

    Lily



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 13, 2019 6:14 AM
  • Hi Lily,

    thank you for your answer. My TransactionOption is not set to Required because I can't use the SSIS transactions.

    That's why i am using SQL statements to BEGIN, COMMIT and Rollback the transaction.

    Could you please try your solution again with the TransactionOption property of Container set as 'Supported'? Thank you. 

    Friday, December 13, 2019 9:18 AM
  • Hi M. Ali Shah,

    Please check if your connection managers are all set correctly.

    And could you share the outputs to offer more error information? That will be helpful for debugging the package.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 13, 2019 10:02 AM
  • Hi @Lily Lii

    please see the screenshots below.

    

    Friday, December 13, 2019 12:07 PM
  • Hi M. Ali Shah,

    According to the screenshots, you could modify:

    1. Insert data into SAP Table: Conversion failed when converting the varchar value 'A' to data type smallint.

       Please match your data type in the query by using CAST and CONVERT.

    2. Rollback Transaction: The request ROLLBACK TRANSACTION has no corresponding BEGIN TRANSACTION.

       Rollback transaction should be used in the same session where begin transaction is created, please check your code in this task, it could be:

    begin transaction
    --add your code
    rollback

    Best Regards,

    Lily




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, December 16, 2019 1:56 AM
  • Hi @Lily Lii,

    I intentionally created the error in 'Insert data into SAP Table' task to show you that i get the error on task 'Rollback Transaction'.

    As you can see above in the data flow, both create transaction and Rollback transaction are in the same container.

    I can't understand why i get 'The request ROLLBACK TRANSACTION has no corresponding BEGIN TRANSACTION' error.

    Best Regards,

    Muhammad

    Monday, December 16, 2019 10:51 AM
  • Hi Muhammad,

    'Begin transaction' and 'Rollback' are not dividing independent component. Once you have closed the connection, all your uncommitted transaction get rollbacked, if not explicit committed(Started with begin transaction statement).

    Easy way to rollback a transaction

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by M. Ali Shah Tuesday, December 17, 2019 10:17 AM
    Monday, December 16, 2019 11:21 AM
  • Hi Muhammad,

    'Begin transaction' and 'Rollback' are not dividing independent component. Once you have closed the connection, all your uncommitted transaction get rollbacked, if not explicit committed(Started with begin transaction statement).

    Easy way to rollback a transaction

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    so you are suggesting not to explicitily use 'Rollback Transaction' in case of task failure as it will Rollback in any case? 
    Monday, December 16, 2019 12:27 PM
  • I do not think that a TRANSACTION block works among the tasks with a Data Flow task. I would only add the TRANSACTION block inside the Execute SQL task which insert data to the SAP table. It does not matter if the task importing data to the staging table fails since you can re-run the package.

    A Fan of SSIS, SSRS and SSAS

    Monday, December 16, 2019 2:42 PM
  • @Guoxiong

    i think it works with the task because it COMMIT is successfully executed if the data flow and execute SP tasks are successfully executed.

    Anyhow, i was trying different things and figured out that the TRANSACTION Rollbacks itself if the task 'Insert data into SAP Table' fails. So, i removed the link from 'Insert data into SAP Table' to 'Rollback Transaction'. Does this make sense? 


    • Edited by M. Ali Shah Monday, December 16, 2019 3:28 PM
    Monday, December 16, 2019 3:25 PM
  • I am still wondering if the ROLLBACK would work if the Data Flow task fails. Did you test it using the failed file? You still can log the failure to the SAP_FailedImportFiles table if it fails. And also when the package runs every time it will truncate the staging table. So it is not necessary to use the block of the TRASACTION statement.

    A Fan of SSIS, SSRS and SSAS

    Monday, December 16, 2019 4:18 PM
  • @Guoxiong

    'Rollback Transaction' works if the Data flow task fails. I tested it with following 2 scenarios

    1. Provided a wrong file to the Data flow task.

    2. Created an error in row nr. 287,000 in a file containing 300,000 records

    But you are right, I can maybe remove the 'Rollback Transaction' tasks in my Control Flow. 

    One other thing I noticed is that if the task 'Insert data into SAP Table' fails, no records exists in the Staging table and also no data is inserted to the SAP table. SQL automatically rollsback the complete transaction. This works only when the 'Begin Transaction' task is there.

    So i guess I should keep 'Begin Transaction' and 'Commit Transaction' tasks and remove the 'Rollback Transaction' task. Right?



    • Edited by M. Ali Shah Monday, December 16, 2019 4:46 PM
    Monday, December 16, 2019 4:30 PM
  • If you keep the 'Begin Transaction' and 'Commit Transaction' tasks, the transaction would still roll back even if the task 'Insert data into SAP Table' succeeds, but any one of three other tasks within the 'Begin Transaction' and 'Commit Transaction' tasks fails. Not sure if you want this scenario.

    A Fan of SSIS, SSRS and SSAS

    Monday, December 16, 2019 4:53 PM
  • Hi Muhammad,

    Does 'Remove Rollback Transaction' work for you? It's so kind of you to mark helpful replies as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, December 17, 2019 8:34 AM
  • If you keep the 'Begin Transaction' and 'Commit Transaction' tasks, the transaction would still roll back even if the task 'Insert data into SAP Table' succeeds, but any one of three other tasks within the 'Begin Transaction' and 'Commit Transaction' tasks fails. Not sure if you want this scenario.

    A Fan of SSIS, SSRS and SSAS

    Hi @Guoxiong

    I don't expect these tasks to fail but i should maybe still add a failure path to these tasks!

    Tuesday, December 17, 2019 9:39 AM