How to update Azure sql database table from Azure Datafactory in a foreach loop?

Jay V 21 Reputation points
2021-03-01T22:35:53.33+00:00

I have a database table that will provide a large number of ids (500k). For each id I will need to make a REST api call (to Yammer), extract a value from the response JSON and then update the source table against the id with the extracted value.
id1 : value1
id2 : value2
id3 : value3
What kind of activities and configurations will I need to use to make it simple, fast and efficient? I am fairly new to Azure and learning about lookup and foreach activities. I see that lookup activity has a 5000 row limit.
How can I chunk the data read from the database?
How can I update the row in the table for each id? Is stored procedure activity the only way? Is it efficient to call a stored procedure for thousands of times?

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

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2021-03-02T22:26:10.31+00:00

    Hello @Jay V ,
    Thanks for the ask and using the Microsoft Q&A platform .
    Your assesment is correct that lookup has a limit of 5K rows at this time . Since you have mentioned that source is a SQL table and with 500K records I am assuming that you must be having a primary key , if thats the case you can use the watermark logic and it should work . Read about the logic here
    Logic .

    1. Create a Watermark table to store the max Primary key .
    2. Fetch the top 5K from the table and pass that to the foreach ( FE) loop .
    3. Try to grab the max of the Primary key and now the query should be something like ( SELECT * from somesource table where primarykey > max(primarykey )

    Let me know if you have any queries on this

    Please do let me know how it goes .
    Thanks
    Himanshu
    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

    1 person found this answer helpful.