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 Pagination of large result. In OData specification data may come in JSON format or XML format. For XML format check this article. This article will cover mostly OData JSON API example.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

REST API Pagination 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. There are few pagination methods covered in this article.

Reading OData API in SSIS using JSON Source

Lets talk about how read OData APi and Paginate Automatically. Check below typical OData service Response (JSON Format). Notice how it includes nextLink attribute which points to next URL to fetch more data. When you set Data Format=OData in JSON Source it will take care of pagination for you.

Request URL:

Response:

Step-By-Step

In this section you will learn how to use JSON Source Adapter to extract data from OData API.

  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
    SSIS Data Flow Task - Drag and Drop
  4. Double click on the Data Flow task to see Data Flow designer surface.
  5. From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
    SSIS JSON Source - Drag and Drop
  6. As you can see when more data needed to complete resultset OData result includes nextLink attribute.
  7. If you are using SSIS JSON Source Component then simply select OData format as below screenshot.OData Paging using SSIS JSON Source
  8. Then you can configure Array Filter as below
  9. Click Preview data and OK to save
    Read JSON File data from Web Url Example in SSIS

OData Paging using SSIS JSON SourceOnce 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

Loading OData API into SQL Server / Other Target

Now let's look at how to load data into target like SQL Server, Oracle or Flat File. In below example we will see loading data into SQL Server database but steps may remain same for other targets which can be accessed using OLEDB Drivers (e.g. Oracle).
  1. Inside Data Flow, Drag OLEDB Destination from SSIS Toolbox
  2. Connect our Source component to OLEDB Destination
  3. Double click OLEDB Destination to configure it
  4. Select Target Connection or click NEW to create new connection
    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

    Configure SSIS OLEDB Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS

  5. Select Target Table or click NEW to create new table based on source columns
  6. Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
    Create NEW Table SSIS OLEDB Destination

    Create NEW Table SSIS OLEDB Destination

  7. Click OK to Save OLEDB Destination Settings
  8. That's it, You are now ready to run data flow. NOTE: If you wish to debug data flow and see records when you run, add data viewer by right click on blue arrow > Click Enable Data Viewer
  9. To execute data flow, Right click anywhere inside Data Flow Surface and click Execute Task
 

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 (File/REST) and tagged , , , , , .