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
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.
Once you execute package see how it will pull entire resultset by looping through next urls (until its null)
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.
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.