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 am also getting the same issue. Insert works / Upsert does not. The only thing I've noticed is that the key column type is wrong. My dataset source AND sink are both integer, but shows abc in the key list?? The dynamic content is also disabled, so can't wrap with toInteger(ID)
I've tried all different methods but no joy in getting this to work.

My only solution is to use a Data Flow with source > Alter Row> sink. (This does work)

Please can this be looked into

Answer 2

I also had the same issue. I found that my issue was related to my Azure SQL Sizing. I was running a cheap 10DTU Azure SQL and it kept timing out. I bumped it to 100 DTU and the import worked just fine. I will be trying different DTU sizes to see if I can find the lowest DTU for my use case.

Answer 3

Hello again @Mike McGuinness . Thanks for making this a separate thread.

Reiterating from my parting comments in previous question thread, this error occurs when a writebatch takes too long to write.
However, looking at your copy details, I am more concerned that NO data was written. As a test to determine whether this is due to writebatch or something else, I'd like you to try setting write batch size to 1. If it can't write a single row, then something else is deeply wrong.

Also, just to rule out the unlikely, how big is a single row of data?

I see there are "interim rows" in your copy activity details. This suggests polybase or staging. Can you tell me what sort of settings you have on that?

Answer 4

This is probably not the answer you are hoping for but I now set these pipelines up as Oracle to Parquet on blob then truncate the temp table in azure sql myself using a pre copy command followed by Parquet to Azure SQL. When the copying is finished I run a stored procedure which merges the temp and prod tables.

This has worked perfectly everytime for the last few weeks.

Answer 5

Any luck here? Even I'm getting the same issue when I tried to upsert data using copy activity.

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=,'

I tried below options

• Unchecking the Use Tempdb option to "Select user DB schema" to dbo schema.
• Set "Write batch size" to 10000 rows
• Set "Write batch timeout" to 00:30:00 (30 mins)

None of the options worked. :-(

Also, I'm not able to add Dynamic Content for Key columns textbox. I tried this to pass keys dynamically from parameters unfortunately no parameters were showing up in the dynamic content for Key Columns

Can you suggest???