Synapse- The timeout period elapsed prior to completion of the operation or the server is not responding

Question

Hi

I am seeing this timeout error when copying to synapse using staged copy from OnPrem(SQL Server) to Synapse(using polybase).

Looking at the error it says Synapse is timing out, but Synapse metrics shows its not touched its peak,

we get this error when multiple copy/pipeline activity runs in parallel.

The flow is
SQL-server(OnPrem) -->IR(Azure VM) --> ADF(Polybase) --> Azure Synapse

FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error happened when loading data into SQL Data Warehouse.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.,Source=.Net SqlClient Data Provider,SqlErrorNumber=-2,Class=11,ErrorCode=-2146232060,State=0,Errors=[{Class=11,Number=-2,State=0,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.,},],''Type=System.ComponentModel.Win32Exception,Message=The wait operation timed out,Source=,'",
"failureType": "UserError",
"target": "cp_Load_SQLServer_to_Synapse_Polybase",
"details": []

Answer 1

Hi Himanshu

Yes we resolved the timeout issue having batch count to 10 within for loop.

I think Synapse with 400 DWU it can handle only 14 active connections, so after adding the batch count it worked.

Thanks
Harish