CODEWITHSUNDEEP
azureazure-data-factoryetlazure-synapse
AntonyJ
AntonyJ

Reputation: 29

Azure Synapse (Azure Data Factory) REST source pagination with dynamic limit

In Azure Synapse I'm performing a data copy action. It has a REST API as a source and needs to store this data in a on-premise SQL table. The whole setup is configured and the table is filled with 100 records. This is a limit from the API. It returns 100 records by default. The total amount of data that I need to collect from this endpoint is somewhere around the 150000 records and grows by day.

I saw that pagination might help me here. That everything when 100 records are collected I can start collecting the next 100 records up and until I've reached the total data set. I don't want to set the total limit in the configuration, I would like to see that it is being collected dynamically until it reaches the maximum by itself.

How can I set this up?

Thanks in advance!

I've set pagination with header value 'skip' and this refers to the relative URL which I defined like: ?take=100&skip={skip}. I've tried to work with the value parameters but I've no clue how to set that up.

Upvotes: 0

Views: 1221

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8382

I don't want to set the total limit in the configuration, I would like to see that it is being collected dynamically until it reaches the maximum by itself.

To achieve this scenario, we need to Follow 2 steps:

  1. Set "AbsoluteUrl.offset": "RANGE:0::20", In the range of Pagination rules and leave the end of range empty. Here it will start from 0 and end is not defined with offset of 20.
  2. Set End Condition rules according to different last responses. otherwise, pipeline run will not stop as we have not provided any end to range of pagination Here I used the pagination ends when the value of the header key in response equals to user-defined const value as my data contain id if it is equal to 2000 it will stop the pipeline run

enter image description here

  • My sample data: It contains around 100 objects enter image description here

  • My dataset setting: enter image description here

  • Preview after pagination rule: I have set offset as 20 and limit/take as 1 because of this it is showing only 5 objects (just to check if it is going till last data or not). enter image description here

  • pipeline completed successfully enter image description here

refer Microsoft document for more understanding on pagination rule

Upvotes: 0

Related Questions