ADF REST API Pagination rules EndCondition for empty response

Question

I'm trying to configure a Copy activity in ADF that uses a REST API linked service/dataset as the Source, but I'm struggling with configuring the correct Pagination rules.

The REST API uses a query parameter in the URL called "page" to paginate the responses, which needs to be incremented sequentially (i.e. 1, 2, 3 etc.) until there are no more records.

When there are no more records for the current or subsequent pages, the REST API returns an empty JSON array (i.e. "[]") in the body.

I've configured the Pagination rules for the Source of the Copy activity as follows:

Name	Value
QueryParameter: page	Range: 1, <no value>, 1
EndCondition: $	Empty
MaxRequestNumber	10

However, when I run my pipeline the Copy activity fails with the following error:

Failure happened on 'Source' side. ErrorCode=RestMoreThanOneObjectsReturned,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=More than one object returned.\r\n'System.Collections.Generic.List`1[Newtonsoft.Json.Linq.JObject]',Source=Microsoft.DataTransfer.ClientLibrary,'

(I get the same behaviour if I change the EndCondition rule value to NonExist)

I might be misinterpreting what's happening, but it looks like the EndCondition I've configured can't be evaluated when the array in the response isn't empty (and contains several elements) for preceding pages.

What am I doing wrong, and what are the correct Pagination rules for my circumstances?

Answer 1

@Jez Walters Welcome to Microsoft Q&A platform and thanks for posting your question.

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: I'm trying to configure a Copy activity in ADF that uses a REST API linked service/dataset as the Source, but I'm struggling with configuring the correct Pagination rules.

The REST API uses a query parameter in the URL called "page" to paginate the responses, which needs to be incremented sequentially (i.e. 1, 2, 3 etc.) until there are no more records.

When there are no more records for the current or subsequent pages, the REST API returns an empty JSON array (i.e. "[]") in the body.

I've configured the Pagination rules for the Source of the Copy activity as follows:

Expand table

Name	Value
QueryParameter: page	Range: 1, <no value>, 1
QueryParameter: page	Range: 1, <no value>, 1
EndCondition: $	Empty
MaxRequestNumber	10

However, when I run my pipeline the Copy activity fails with the following error:

Failure happened on 'Source' side. ErrorCode=RestMoreThanOneObjectsReturned,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=More than one object returned.\r\n'System.Collections.Generic.List`1[Newtonsoft.Json.Linq.JObject]',Source=Microsoft.DataTransfer.ClientLibrary,'

(I get the same behaviour if I change the EndCondition rule value to NonExist)

I might be misinterpreting what's happening, but it looks like the EndCondition I've configured can't be evaluated when the array in the response isn't empty (and contains several elements) for preceding pages.

What am I doing wrong, and what are the correct Pagination rules for my circumstances?

Solution: I fixed my problem by creating a pipeline with an Until activity as follows:

1. Two Set variable activities, to get and then increment the page number (used in step 2)
2. A Copy activity, with a Source to make the REST API GET request (using the page number from step 1 in the URL) and a Sink to write the JSON response to blob storage (appending the page number from step 1 to the file name)
3. Another Set variable activity, to count the number of items in the JSON response.
4. A Wait activity, to wait for 1 second (to implement throttling)

I made the Until activity run until the item count (from step 3) is zero or the page number (from step 1) is more than 100 (to prevent infinite looping).

One small drawback with this approach is that an empty JSON file is written on the last execution of the Until activity.

I tried replacing the Copy activity with suitable Web activities (to get the response and save it if not empty), but the Web activity to save the response kept failing intermittently with incorrect Content-Length failures (despite using length(variable('Body')) to set the value).

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.