locked
ErrorCode": "2200" "ErrorCode=UserErrorSqlBatchWriteRollbackFailed" RRS feed

  • Question

  • Hi everyone!

    I'm doing an ETL in Data Factory and I got an error in a simple Full Join between two tables step. The main table has 40million+ rows, but the query stops at 31 million and Data Factory shows the following message:

    Error
    {

    "errorCode": "2200", "message": "ErrorCode=UserErrorSqlBatchWriteRollbackFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Exception in SQL batch write and failed to rollback.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidOperationException,Message=This SqlTransaction has completed; it is no longer usable.,Source=System.Data,'", "failureType": "UserError", "target": "STG_VolumeMargem_PV" }

    Obs1: All tables are in a Azure SQL DW instance.

    Obs2: Data Factory writes the 31 million rows in target table.

    Does anyone has an idea what's this about?

    Thanks!

    Andre Rugere

    Friday, November 1, 2019 5:47 PM

All replies

  • Hi andrerugere,

    I have noticed a few customers encountered similar error when there is a performance/memory issue. Please consider below action plan.

    • The S0 database tier may not have adequate resources to handle high memory consuming data import processes. 
    • Consider batching the import process to handle smaller workloads to avoid the high wait stats on both WRITELOG and Memory_Allocation_Ext

    Hope this helps.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, November 2, 2019 12:56 AM
  • You should use dataflow which is built for scale to solve ETL scenarios
    • Proposed as answer by dataflowuser Saturday, November 2, 2019 2:49 PM
    Saturday, November 2, 2019 2:49 PM
  • Hi andrerugere,

    Just checking to see if the previous comments was helpful? If you still have further query, please let us know.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, November 4, 2019 6:45 PM
  • Hi andrerugere,

    Following up to see if you still need assistance on this query. If you have already found a solution, please consider sharing it here, so that it will be helpful for other members of the community who reads this thread.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, November 5, 2019 11:20 PM