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

Question

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?

Answer 1

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