Copy Acitivity Failed while copying data from SQL Server in Azure VM to Azure SQL Database

Question

Hi,

We recently hit the copy acaitivty failed couple times when copying data from SQL Server in Azure VM to Azure SQL Database, also same issue happen when copying data from Azure SQL Database to another Azure SQL Database. Could you help to provide some suggestions? Thanks.
Here’s more details:
We got several pipeline running at a regular time to loading the data once a day. There’s a for each loop in the pipeline to load all the table at the same time.
The failed happen while copying data for one or two medium size table (between 200MB to 500MB), as indicated in the error log and performance snapshot. It’s not happening every day, which make it difficult to recreate the issue manually. Same issue happen when copy data between different Azure SQL Database. Any idea would be helpful.

Source: SQL Server in Azure VM or Azure SQL Database (elastic pool)
Sink: Azure SQL Database (50 DTU)

Error Log:
Error code2200
Troubleshooting guide
Failure typeUser configuration issue
DetailsErrorCode=SqlBatchWriteTransactionFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=SQL transaction commits failed,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.),Source=.Net SqlClient Data Provider,SqlErrorNumber=121,Class=20,ErrorCode=-2146232060,State=0,Errors=[{Class=20,Number=121,State=0,Message=A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.),},],''Type=System.ComponentModel.Win32Exception,Message=The semaphore timeout period has expired,Source=,'

Answer 1

Hi @Stephen Huo ,

Welcome to Microsoft Q&A forum and thanks for your query.

As called out in the ADF Troubleshooting doc, could you please try updating the SQL-linked service connection string with a connection timeout value that's equal to or greater than 120 and see if that helps to resolve the issue.

Hope this helps. Let us know how it goes.

Thank you