Copy Activity is Failing while merging the data into SQL DB

[SivaReddy24680]

Hi Team,

We are using a Copy activity to extract data from HANA DB into Fabric. For low-volume tables, we perform a truncate and reload, whereas for high-volume tables, we extract incremental data based on a Year or Date field.

Currently, we are loading around 60 tables from HANA to the SQL DB in Fabric:

• 45 tables use full load

• 15 tables use incremental load

Almost all tables load successfully. However, we are consistently encountering issues with 2 specific tables, where the load fails in 9 out of 10 runs.

Has anyone else experienced a similar issue? Any insights or suggestions would be much appreciated.

Below is the error we are getting:
{
"errorCode": "2200",
"message": "ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.Connectors.MSSQL,''Type=Microsoft.Data.SqlClient.SqlException,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.\r\nOperation cancelled by user.\r\nThe statement has been terminated.,Source=Framework Microsoft SqlClient Data Provider,''Type=System.ComponentModel.Win32Exception,Message=The wait operation timed out,Source=,'",
"failureType": "UserError",
"target": "Copy Delta data",
"details": []
}

I can see the data copied(took 29 minutes) from the source but, failed at the destination (failed after 2 hrs 32 mins)

[v-tsaipranay]

Hi @SivaReddy24680 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @SivaReddy24680 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @SivaReddy24680 ,

Thank you for reaching out to the Microsoft Fabric Community.

Based on your explanation, the data is being read successfully from the HANA source, but the process is failing when trying to write to the SQL table in Microsoft Fabric. The error messages like "Execution Timeout Expired" and "The wait operation timed out" usually happen when the system takes too long to write a large amount of data especially when using operations like merge or upsert.

To fix this, you can try increasing the command timeout setting in your Copy Activity, so the system has more time to finish the write operation. Also, enabling batch writes can help break the data into smaller chunks, which often makes the load more reliable. Another approach is to first load the data into a temporary staging table and then move it into your final table using a stored procedure or notebook. This gives you more control and avoids writing everything in one long operation. It’s also a good idea to check if there are any triggers, indexes, or other activities on the SQL table that might be slowing things down.

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[v-tsaipranay]

Hi @SivaReddy24680 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.