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.
PrerequisitesBefore we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- 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.
In this section you will learn how to use JSON Source Adapter to extract data from OData API.
- Firstly, You need to Download and Install SSIS ZappySys PowerPack.
- Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
- Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
- Double click on the Data Flow task to see Data Flow designer surface.
- From the SSIS toolbox drag and drop JSON Source on the dataflow designer surface.
- 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.
- Then you can configure Array Filter as below
- Click Preview data and OK to save
OData Paging using SSIS JSON SourceOnce you execute package see how it will pull entire resultset by looping through next urls (until its null)
Loading OData API into SQL Server / Other Target
- Inside Data Flow, Drag OLEDB Destination from SSIS Toolbox
- Connect our Source component to OLEDB Destination
- Double click OLEDB Destination to configure it
- Select Target Connection or click NEW to create new connection
- Select Target Table or click NEW to create new table based on source columns
- Click on Mappings Tab to Auto map columns by name. You can change mappings as you need
- Click OK to Save OLEDB Destination Settings
- 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
- 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.
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.