loop in azure synapse until a parameter is found in Json response

Bhoopathiraju Deepthi 66 Reputation points
2021-09-06T15:33:14.853+00:00

I have a requirement where i need i need to pull out objects from Rest API.
I was able to send the get request and getting JSON response.
There are two scenarios needs to be handled as per server limits and pagination of response.

  1. The default result limit is 1000 or whatever we choose to set it depending on getting pagecount as attached in below screenshots.
  2. if the count is more than 1000 it returns an URL which needs to be captured and continue the get request until complete response is returned.
    4.if the count<=1000 there will not any URL appended at the end of result.

Attaching how the response looks and conditions on server side.

there is no any count parameter which gives the total records value in response. we can either get the page count and use it to set the page size else if we dont mention anything then deafult will be 1000.

Not sure what will be the feasible approach.

129599-image.png

129629-image.png

129682-image.png

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,547 questions
Microsoft Partner Center API
Microsoft Partner Center API
Microsoft Partner Center: A Microsoft website for partners that provides access to product support, a partner community, and other partner services.API: A software intermediary that allows two applications to interact with each other.
320 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,046 Reputation points
    2021-09-07T21:36:55.103+00:00

    Hello @Bhoopathiraju Deepthi and welcome to Microsoft Q&A.

    It looks like this service uses the skiptoken to do pagination. This means you do not need to count records to paginate.

    Since it passes the full URL, we can make use of the AbsoluteURL pagination option of the REST type dataset. This option takes the odata.nextlink and uses that for the next call, and stops when there no nextlink is returned.

    Another option, since this is OData, is to use the OData type dataset.