I am struggling with implementing pagination for a REST API in a Microsoft Fabric Data Factory pipeline. I have read the official Microsoft documentation and have tried various things but to no avail. In my scenario, I am using a Copy Data activity which connects to the API endpoint and outputs the resultant JSON file to a lakehouse folder.

The API endpoint URL is in the following format (example):

={startdatetime}&end={enddatetime}&limit=200&page={page}

The REST API returns the following values in the response body:

{
    "total_count": 325,
    "page": 1,
    "total_pages": 2,
    "result": [ array of result objects ]
}

The Source connection details are as follows (I have not specified a value for the page parameter in the query string):

Relative URL: :00:00Z&end=2025-01-29T00:00:00Z&limit=200&page=

Pagination rules:

My assumption was that Data Factory would dynamically increase the value of the page parameter by 1 until no more data was returned. I have tried variations where the 'page' parameter is wrapped in curly braces like so: {page} but no matter what, I only ever get the first page of results returned.

I'd like to avoid having to wrap the activity in a loop, as it is already in a loop because I need to fetch data for a range of days and nested loops are not supported in Data Factory. I'm aware that I could use a child pipeline for the inner loop, but I want to try see if the pagination will work first before I go down that route.

I am struggling with implementing pagination for a REST API in a Microsoft Fabric Data Factory pipeline. I have read the official Microsoft documentation and have tried various things but to no avail. In my scenario, I am using a Copy Data activity which connects to the API endpoint and outputs the resultant JSON file to a lakehouse folder.

The API endpoint URL is in the following format (example):

https://api.somecompany/data?start={startdatetime}&end={enddatetime}&limit=200&page={page}

The REST API returns the following values in the response body:

{
    "total_count": 325,
    "page": 1,
    "total_pages": 2,
    "result": [ array of result objects ]
}

The Source connection details are as follows (I have not specified a value for the page parameter in the query string):

Relative URL: https://api.somecompany/data?start=2025-01-29T00:00:00Z&end=2025-01-29T00:00:00Z&limit=200&page=

Pagination rules:

My assumption was that Data Factory would dynamically increase the value of the page parameter by 1 until no more data was returned. I have tried variations where the 'page' parameter is wrapped in curly braces like so: {page} but no matter what, I only ever get the first page of results returned.

I'd like to avoid having to wrap the activity in a loop, as it is already in a loop because I need to fetch data for a range of days and nested loops are not supported in Data Factory. I'm aware that I could use a child pipeline for the inner loop, but I want to try see if the pagination will work first before I go down that route.

• rest
• pagination
• azure-data-factory
• microsoft-fabric
• copy-data

Share Improve this question Follow asked Jan 29 at 17:52 JazzaJazza 1,08211 gold badge99 silver badges2424 bronze badges 2

• Are you getting any error? – Rakesh Govindula Commented Jan 30 at 2:07
• @RakeshGovindula no, the Copy Activity succeeds but I only ever get the first page of results. – Jazza Commented Jan 30 at 9:44

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

You can use below configurations in the copy activity source REST API to achieve your requirement.

First give the page number value with any parameter in between {} in the dataset Relative URL as shown below.

?page={Offset}&size=20

In the copy activity source, give the Range and End condition as below.

EndCondition - $.<your_object> - Nonexist
QueryParameter - {Offset} - Range - <your_page_start_value>,End,1

Give your sink and debug the pipeline. Now, all data from the REST API pages will be copied to the target location as below sample.