1

I have a lookup activity that timeouts after the 2 hours (120 mins) which is the default (could be just a coincidence) even after increasing the query timeout to 720 mins. The lookup activity executes a Proc based on an expression.

enter image description here

This is the error

Failure happened on 'Source' side. ErrorCode=UserErrorSourceQueryTimeout,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Query source database timeout after '7200' seconds.,Source=Microsoft.DataTransfer.DataContracts,''Type=System.TimeoutException,Message=,Source=Microsoft.DataTransfer.DataContracts,'

enter image description here

enter image description here

Is there a step that I've missed out somewhere?

Geezer
  • 513
  • 5
  • 17
  • I mean two hours is a long long time in stored proc terms. Consider resolving that performance issue first, with indexing, tuning etc. Consider splitting it up into two steps where a Stored Proc activity executes the proc, or maybe precompile that data overnight? And then the Lookup is only looking up data in a table. – wBob Jul 20 '22 at 07:26
  • the ADF pipeline that the Lookup activity belongs to forms part of an overnight DWH ETL process, This particular SP runs long only occoassionaly. – Geezer Jul 20 '22 at 07:39

1 Answers1

0

There are actually two timeouts in Lookup activity, one is Lookup activity timeout and other is queryTimeout. Please make sure that to set the queryTimeout value lower than the lookup activity timeout value. queryTimeout won't be effective if it is greater than lookup activity timeout (24hrs).

Note: When you use query or stored procedure to lookup data, make sure to return one and exact one result set. Otherwise, Lookup activity fails.

Refer: Lookup activity in Azure Data Factory and Azure Synapse Analytics

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14
  • the `queryTimeout` value (720mins) is lower than the `lookup activity timeout` value (7 days default) so this doesn't help – Geezer Jul 20 '22 at 07:45