Synapse/ADF Pipeline to Make HTTP API Request for every record in a database table

Question

I have a table with 120,000 records in it. This will be my source table.
For each record in the table, I want to send a request to a REST API and then make a stored procedure call. There are reasons this has to be done and one by one of which I don't want to elaborate here.

I know I can do this with a Lookup Activity that will look up the table (to a max of 5,000) items and then I can do a For Each on this array and for each iteration of the For Each I use a Web Activity to make the REST API request. And then call the stored procedure activity.

However, to do this, I need to add a bunch of paging variables (skip, etc) to the stored procedure or query. Then I have to have a nested For Each (the first ForEach does the paging and the lookup activity is inside it.. the second For Each iterates each record within the page set). But, you can't nest a For Each inside another ForEach, so I have to create another pipeline and execute that from within the foreach to do it's own foreach and pass in the array to that pipeline.

Is this convoluted way of doing this really the only way?

This is for a very simple process of doing the following: For each record { Call Rest API }

Answer 1

As lookup has the limitation of 5000 rows, You need to Design a two-level pipeline where the outer pipeline iterates over an inner pipeline, which retrieves data that doesn't exceed the maximum rows or size. As per document.

So, the possible solution would be, paginate with lookup query with variables then pass this output to another pipelines parameter to iterate on it and call the rest API and Stored procedure respectively.

To paginate the query, you can follow below format.

• First take the variable with name counter and initial value as @string(0) - 0 as below:
• Then take until activity where you can loop the records until it gets finished. with condition @equals('120000', variables('counter'))
• Inside until take variable to store the incremented value as tempcouner ith value variables('counter')
• Now you can use this variable to paginate your query with fetch and offset. In offset you can keep value of counter variable created and fetch is constant 5000 rows.
• Now Paas the output to variable of another pipeline to iterate on output and call that pipeline with execute pipeline activity as nested loop is not supported in ADF.
• Now take another set variable to increase initial value as @string(add(int(variables('tempCounter')),5000)) it will add 5000 to initial value.