Forum Discussion

halll_sc's avatar
halll_sc
Frequent Visitor
1 year ago
Solved

API Loop

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. 


3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

  • 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's avatar
      halll_sc
      Frequent Visitor

      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 ?