CODEWITHSUNDEEP
azureazure-data-factory
BlakeB9
BlakeB9

Reputation: 647

User configuration issue in Data Factory (Upsert Timeouts)

So I've got a data factory that does a lookup to gather tables from a couple schemas as well as key columns for said tables so that I can use Upsert when copying the data over to another database.

I've noticed that quite a few tables are throwing me this error:

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. Operation cancelled by user. 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=11,Number=0,State=0,Message=Operation cancelled by user.,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],''Type=System.ComponentModel.Win32Exception,Message=The wait operation timed out,Source=,'

Looking at the tables where this error occurs, the process is taking over 2 hours. Is this just an issue with trying to use Upsert with big tables (that have hundreds of thousands/millions of records)? When I ran this recently, the bulk of the data was already in my sink database, meaning there only needed to be a few records added (not literally a few records, but in the grand scheme of the table, not many) to the table. Which leads me to believe that the comparisons that Data Factory is making to determine if records need to be added or updated in a table is what is ultimately causing the timeout.

Is that what's happening here? Is there anything I can do to either not cause the timeout to happen and/or speed up the pipeline in any way?

Upvotes: 0

Views: 721

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8382

The cause of error might be query is taking too much time to execute. it may be because of large volume of data.

To resolve this, you can try several options:

  • Increase the connection timeout of the SQL linked service as below. enter image description here
  • Try to play with these values so that it will transfer the data in the form of batches and increase batch timeout so query will not get timed out. enter image description here

Upvotes: 1

Related Questions