ADF : Clarity on Lookup Activity Timeout values with SQL

Mike2020 21 Reputation points
2021-03-10T17:59:25.997+00:00

Hi

In Azure Data Factory, we have a lookup activity with an SQL Database.

There are config items on the general tab of the activity for timeout/retry/retry interval.

In the settings tab, there is a "Query timeout (minutes)" config item.

Can we have some clarity on the two timeout please and best practice? For example, one would assume it is best practice to set the "Query timeout (minutes)" to be lower than the activity timeout value?

We would like to ensure that your basic sql query timeout get handled correctly with opportunity to retry according to the retry config items against the activity.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
7,173 questions
0 comments No comments
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 39,007 Reputation points Microsoft Employee
    2021-03-12T06:59:29.44+00:00

    Hi @Mike2020 ,

    Welcome to Microsoft Q&A forum and thanks for reaching.

    Sorry for the delay in response. As called out in the lookup activity documentation, the maximum amount of time a lookup activity can run before timeout is 24hrs. This 24 hours timeout limit is at lookup activity level. For some types of dataset (like SQL), you can specify queryTimeout, which takes effect on the query. This timeout won't be effective if it is greater than lookup activity timeout (24hrs).

    77046-image.png

    As a best practice it is better to set the queryTimeout value lower than the lookup activity timeout value. And yes, in case if your SQL query timeout for some reason, you can utilize the Retry (maximum number of retry attempts before lookup activity status reported as failed) and Retry Interval (the number of seconds between each retry attempt of the activity) properties as needed to avoid activity/pipeline failures.

    Hope this info helps. Do let us know if you have further query.

    ----------

    Thank you
    Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful