question

MarkE-5319 avatar image
0 Votes"
MarkE-5319 asked MartinJaffer-MSFT answered

API pagination & Data Factory

Hi,
I’m working on pulling a data source in via ADF and have the connection all setup and working.
The problem I’ve got is that the API will only return a max of 50 records but does present the following in the JSON.

"paging": {
"offset": 0,
"limit": 50,
"total": 85
}

So I know there are more records there but cannot for the life of me work t the best way to have ADF cycle through and have failed to find a representative example as yet. Any help appreciated in pointing me in the right direction here.

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MarkE-5319 did my answer help?

Also see this related thread for another solution:
https://docs.microsoft.com/answers/answers/67453/view.html

0 Votes 0 ·
MartinJaffer-MSFT avatar image
2 Votes"
MartinJaffer-MSFT answered MartinJaffer-MSFT commented

Hello @MarkE-5319 and thank you for your question.

The pagination support in the REST connector is rather limited. It does not support that form of pagination out-of-the-box. You can help prioritize this feature improvement by voting for it at the feedback forum. There are several requests to choose from:

I can provide you a convoluted work around if you are interested. The work-around involves first using a web activity to get the paging, offset, limit, and total you mentioned, then set up variables for a loop. Inside the loop there is copy activity and set variable activities. The drawback is each copy activity produces a separate files.
To get around the self-referencing variable limitation, I use 2 variables, X and Y. First I set Y = X + 1. Then I set X = Y. That is how I increment variables.

Let me know if you are interested, and I will provide pictures.

Thank you for your patience,
Martin.

· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MarkE-5319 Please let us knwo if you still need assistance.
If my answers helped you, please mark as answered or upvote.
Otherwise, if you found your own solution, please share it with the community to help others with the same question.

Thank you!

0 Votes 0 ·

HI, I am working on a similar issue. Would it be possible to get the pictures for the workaround? Thanks!

0 Votes 0 ·

@MartinJaffer-MSFT While I don't have this problem right now, I would love to see pics of that solution - I am sure it will come up at some stage.

0 Votes 0 ·

@MartinJaffer-MSFT I also had same issue, was able to solve with for loop with copy activity and few variable sets. Only thing is it generates unnecessary part files (if I can call them part files XD) and takes much more time to execute. Also with pagination I was able to get the data but it contains the array of Json objects. Do u know how to retrieve info from that file to simple 1 Json file ? I don't wanna use data flow or data bricks . Anyways your logic to increment the variable helped a lot . Thanks

0 Votes 0 ·

Hi K, did you use a 'TotalRows' to terminate your FOR loop? My API doesnt return a figure, it suggests to keep going until no records are returned however I am unsure how to test for this?
C.

0 Votes 0 ·

Yes, I used similar field from the response that gave total records. Until loop can be used I think for your requirement.

0 Votes 0 ·
Show more comments
CoreyEdwards-0634 avatar image
0 Votes"
CoreyEdwards-0634 answered

Did the convoluted workaround get posted? Also have this problem...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

New features for handling pagination have been added since this question was originally asked.
See https://docs.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#pagination-support

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.