API pagination by ID with Azure data factory

Question

Dear Azure community,

I am making a datawarehouse with Azure data factory. I have an API that has a limit of 1000 records. I want to have all records without having to make a 100 separate API calls , because that is not a durable ETL solution.

I've made a pagination solution before by using the pagination function of data factory and the absolute.url. But that was a different API. This API doesn't have that. This API only gives a result message like this:

"Result": {
    "ErrorCode": "0x0000",
    "Description": "Successful"
}

At the end of the API call. And doesn't give any links or anything usefull to use for pagination.
The body of the API looks like this:
{
"StartRecordID":"1",
"EndRecordID":"1000000000"
}

I've been in contact with the API provider and they told me the following:

"We would like to inform you that API pagination works based on the StartRecordID and EndRecordID and it always returns 1000 records based on filters for extension and customer used. If you would like to get further records after first set of records return, we recommend you change the value for "MinRecordId" (i.e. Minimum value of records ID if records after a particular record id are required).
For e.g., value of the last RecordID is 1006 in the first set of result or API response, then value for "MinRecordId" should be 1007."

So they tell me to use "MinRecordId" to grab the following 1000 records. So my question is as following:

How do i use the pagination function of Azure data factory to get all the records of the API call without having absoluteUrls and doing it based on the ID's ?

Thank you for reading my question.
Kind regards,
Fatih

Answer 1

Dear @svijay-MSFT ,

Thank you for your response.
I have created this lookup like you said:

But i don't understand how to put it in the source properties of the copy activity it keeps giving me errors. I filled the request body like this:

{
"StartRecordID":@activity('Lookup last id').output
"EndRecordID":"2000000000"
}

This doesn't seem to work. What's the proper way to write that body ? So it gives me the next 1000 records dynamically.

Kind regards,
Fatih

Answer 2

Hello @Fatih ,

Thanks for the question and using MS Q&A platform.

Sharing a high level approach that I could think and understand if this will meet your requirement.

You ll have to either make use of a web activity/lookup activity - get the LastRecordID from the activity output.

Now you should pass this LastRecordid - you could dynamically fetch this and pass it along the Source Properties of the copy Activity.

Loop the above two process until there are no records returned.

Answer 3

Dear @svijay-MSFT ,

I have found the solution. The solution is this:

 {  
 "StartRecordID":@{string(activity('Lookup last id').output.firstrow.ID)},  
 "EndRecordID":"2000000000"  
 }  

It's your first expression you pointed out to me but that one was missing comma separating the Start and end recordID's. I spotted it after a while. Thank you very much for helping me with this. You have been very helpfull wish you the best.

Kind regards,
Fatih