Pagination in ADF Data flow

Question

I have REST API and need to import the data into a Azure SQL Server table.

I am using Data flow because requirement is to flatten the data.

Because the API produces lot of data I want import data in batches.

Pagination rules are different for Copy activity and Data flow, Copy activity QueryParamters with Range is what I need for Data flow.

I would like to implement below in dynamic way, can you please let me know, how I can get the pagination working.

abc?$expand=xx,yy,zz&$top=400&$skip=0

abc?$expand=xx,yy,zz&$top=400&$skip=400

Example API data: https://services.odata.org/TripPinRESTierService/People?$expand=Trips

Answer 1

In Mapping data flow Range pagination is not supported of Rest API.

1. Using the Filter activity we can generate all numbers in our range using range() function and then the Filter condition with mod to get numbers divisible by 400, ie

Property	Value
Items	@range(1,44000)
Condition	@equals(mod(item(),400),0)

You will get the array with required numbers to be set for offset.

2. Now pass this array to foreach activity as below 3. Create parameter with type string in source Rest DataSource.

4. Pass that parameter as Dynamic value in relative URL. your values are as follows:

base Url: https://services.odata.org/TripPinRESTierService/(S(5omxqz45cvuywr5trycvc53q))/People
realtive Url: @dataset().skipvalue

5. After this give source parameter value as ?$expand=Trips&$top=3&$skip=@{item()} to give the pagination to URL.

OUTPUT: