REST pagination in Azure Data Factory in COPY

Question

I have created a Pipeline in Azure Data Factory that contains a single COPY function. I want to copy from a REST service. Everything is working apart from the pagination.

Here is the response from the REST service (note that for ease of reading I have set it to return a single record in results):

The way that pagination should work is as follows:

1. An initial call to the endpoint will return the first 10 results
2. The "hasMore" variable returned in the body indicates whether there are more results
3. If that is "true" then we should call the endpoint again, this time passing the ID of the last record sent to a query parameter called "startingAfter".

So in the example above the next call to the URL should have the following at the end:

?startingAfter=feb87c25-cb76-4891-b187-2426cd4ece8e

This is all works fine in Postman, but I'm struggling with the pagination settings in Azure. Here's what I've currently got in there:

This doesn't work. It makes the first call and returns 10 records, but then doesn't appear to make any subsequent calls.

Note that if I remove the "QueryParameter" line, it makes 10 calls (before hitting MaxRequestNumber) but of course it brings back the same 10 records each time. So I'm guessing the problem is in the way I've set up the QueryParameter line.

Answer 1

@TC - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: REST pagination in Azure Data Factory in COPY.

Solution: The issue is resolved by @TC .

In the end I find out it was just a syntax issue with the way I was trying to access the last record key. See screenshot below - the "Body" parameter did not like the "$." before the "results[9].id" as I guess it assumes that the result is coming from the body of the response anyway.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

I understood from your scenario that you need to paginate by appending the startingAfter parameter with the ID of the last record from the previous call, you will need to ensure that the pagination settings in your Copy Data activity are correctly configured to handle this logic.

Configure the REST Source

• URL: Enter the base URL of your REST endpoint.
• Request method: GET (assuming you're retrieving data).

Set Up Pagination

Under the pagination tab of the REST source dataset, you need to configure it to understand when and how to fetch the next set of records based on the response of the current request. Since you've mentioned that the service uses a hasMore field to indicate more data and requires the startingAfter query parameter to fetch the next page, you need to set up the pagination rules accordingly.

Pagination Rules

• Absolute URL: Leave this unchecked as you will be using relative pagination with a query parameter.
• Relative URL: This field should be set according to the next page's URL or the next set of parameters. However, ADF doesn't directly support conditional pagination out-of-the-box (checking hasMore directly within the pagination rule). Instead, you will need to use the provided paginationRules to specify the next request's startingAfter parameter based on the previous response's last item's ID. I am not aware if Azure Data Factory UI doesn't allow for complex logic to be entered directly for handling such a conditional parameter based on the hasMore response. Your current setup might not support directly entering a rule that looks at the hasMore flag and then dynamically appends the startingAfter query parameter.

Use Activity Additional Columns for Pagination

A workaround for your specific pagination scenario involves using the Additional columns feature within the Copy Data activity:

• You could use a stored procedure or a custom activity before the Copy Data activity to initially call the API, retrieve the first set of data, and determine the next startingAfter value
• Then, pass this value to the Copy Data activity as an additional column, which can be dynamically used in your REST call
• This would involve manually handling the pagination logic outside of the Copy Data activity, which is not ideal and somewhat against the low-code approach of ADF

Answer 3

Hi TC,

Thank you for posting query in Microsoft Q&A Platform.

In below video, i explained about using APIs with Query Parameters by creating variables in URL. Kindly check it.

Copy activity - Pagination rules - Variables in QueryParameters in Azure Data FactoryHope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.