OData Paging using SSIS – REST API Paging Example

Introduction

In this article we will learn how to use SSIS JSON Source Component to fetch JSON data from OData service and perform OData Paging of large result. We will see Twitter REST API Paging example too where extract JSON Data and Loop through multiple requests will be covered. There are two other paging methods covered in this article.

REST API Paging concept

Most of REST API Services limit size of dataset returned in single request. When more data found it simply includes pointer to next resultset. If you wish to fetch all data (e.g. loop through) then you have to check next resultset indicator and if its not null then you can continue fetching until all data is retrieved.

OData Paging Example using SSIS JSON Source

Lets talk about how Paging is implemented in OData protocol. Check below typical OData service Response.
Request URL: http://services.odata.org/V3/Northwind/Northwind.svc/Order_Details?$format=json
Response:

As you can see when more data needed to complete resultset OData result includes nextLink attribute. If you are using SSIS JSON Source Component then simply select OData format as below screenshot.

OData Paging using SSIS JSON Source

OData Paging using SSIS JSON Source

Once you execute package see how it will pull entire resultset by looping through next urls (until its null)

Fetching entire REST API  resultset using Paging  technique

Fetching entire REST API resultset using Paging technique

Twitter REST API Paging Example using SSIS JSON Source

Twitter REST Api is not OData format but you can see similar type of concept there too. In twitter you don’t have partial URL like OData for next link but it provides you some sort of record identifier. Read here for more information on Twitter REST API Paging technique.

In our case SSIS JSON Source Supports Paging very well so we are covered. To loop through multiple result sets of twitter data simply configure following 3 properties. See below screenshot.

Twitter REST API - Paging Example -Loop through resultset using cursor

Twitter REST API – Paging Example -Loop through resultset using cursor

Conclusion

Based on above examples you can see that handling Raging in REST API Call is not straight forward but using Components like SSIS JSON Source can take some work out of your plate.

Posted in SSIS JSON Source, Uncategorized and tagged , , , , , .