Pagination error for rest api in azure data factory

Question

Data preview pagination

Copy Data SQL

I am trying to copy some data from the rest api into the sql database but i'm only able to copy 20 items (first page) when i debug the pipeline. But there are more than 700 items in the data source.

Pagination tried

I tried this pagination which worked before for a different data source which had after link and was able to copy more than 5000 records into the sql, but the data source i'm working now does not have a after link, but it has values like item_per_page, page_index, total_pages, total_items.

What can i do to copy all the items?

Answer 1

You can follow below to copy all pages from the rest Api into the SQL database:

Create a pipeline and run web activity to retrieve the details of Api as below:

After successful execution of pipeline web activity will retrieve the details of Rest Api, create linked service of Rest Api for source and SQL database linked service for sink. Add foreach activity on success of web activity, add below range function as items for foreach activity:

@range(1,activity('restAPI').output.total_pages)

Inside the for each activity add copy activity, Create Rest Api dataset with dataset parameter rurl with value @dataset().rurl by using created linked service, add it as source of copy activity and set get activity as request method and add value ?page=@{item()} for rurl as shown below:

Create SQL database dataset with dataset parameter tablename for table by using created linked service, add it to sink with value Apipage@{item()} tablename and enable auto create table. Mapp the data according to the requirements. Debug the pipeline and it will execute successfully. The data is copied successfully as shown bellow:

In sample rest Api there are two pages that's why created 2 tables with complete data.