API Loop

[halll_sc]

Good morning, have a question regarding a pipeline that I am building and looking for input on. I am making a call to an API endpoint that will only return up to 500 results. If there are more than 500 results you have to pass a page number into the body of the POST request to return the next page. I have this working right now by using a hard coded range value as the counter value in a ForEach loop. What I am trying to figure out is the best way in the ForEach loop to first check if the API doesn't return an empty JSON array and if it does break the loop and end the flow other wise conduct the CopyData activity. As a note in the API call there is no meta data return by the API to give record count or anything like that. 

My first thought was inside the ForEach loop to 1) create a WebRequest to make a call to the end point 2) create a boolean variable and if api returns result set to TRUE else FALSE. 3) if variable = TRUE process CopyData task else end the flow. 

Seems a bit unnecessary to make another API call just to see if there are results before conducting the CopyData activity. But I can't seem to think of any other way to address this. Any suggestions or feedback would be greatly appreciated. 

[v-nuoc-msft · Accepted solution]

Hi @halll_sc 

You can perform the SQL query activity after the Copy Data activity. Add an sql query to calculate whether 500 records are returned.

How to use Script activity - Microsoft Fabric | Microsoft Learn

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Do it the other way round.  Check if the current call has returned 500 rows. If yes, make the next call. If no, exit the loop.

[halll_sc]

Thank you for the input but the API response doesnt return a row count , so to go down that road I think I would have to perform the copy activity and get the row count of records inserted into the table and if == 500 then call the next API. Or is there another way to return the record count returned from the endpoint ?