Data Factory Copy - SQL Azure Timeout

Question

I have a Copy Data task in a pipeline that keeps timing out. The task is supposed to copy 399,113 rows from a CSV file to an Azure SQL table. Works fine using SSIS.

The ADF sink fails every time after about 2:12 or 2:13 minutes into the "Writing to sink" step.

I increased the Azure SQL Database DTUs from 20 to 50, but it had zero effect.

Operation on target Import AVS SEC Pricing from CSV failed: ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,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. The statement has been terminated.,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.,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],''Type=System.ComponentModel.Win32Exception,Message=The wait operation timed out,Source=,'

Answer 1

I'm also getting a similar error when trying to upsert...a normal copy task works fine.

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,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.

Answer 2

Any update on this issue? I just want to go with Copy activity upsert instead of dataflows and stuck because of this.

Answer 3

Hi everyone, I haven't had any resolution from MS on my threads.

My work-around was to truncate the sink table, remove the primary key index, and just bulk insert records, then re-create the index post import. Fugly, but working.

I suspect the timeout (in my case) is a flaw with ADF using temp DB for upserts when there are lots of records (even though my records were very small and simple). I haven't had time to try using my own staging table yet to see if that fixes the issue.

Answer 4

Hey guys,

Does anybody have solution for timeout issue while copying? I'm also facing the same issue.

Please let me know if you any workaround. Thanks!

Answer 5

I am also having the same issue copying data (using upsert) from one Azure SQL database to another. The table size is about 1.3 GB (~ 57.7 million rows). Copy to interim (TempDB true) table works but from interim to sink it times out after 2 min similar to @Mike McGuinness 's in the original post. Changed to smaller "write batch size" 100000 to 1000, increased DTUs from 10-400 (at 400, it only uses 15% of DTUs) but nothing works.