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 the steps listed in this article, you will need to make sure the following prerequisites are met:- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
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:
|
1 |
https://services.odata.org/V3/Northwind/Northwind.svc/Order_Details?$format=json |
Response:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{ "odata.metadata": "https://services.odata.org/V3/Northwind/Northwind.svc/$metadata#Order_Details", "value": [ { "OrderID": 10248, "ProductID": 11, "Discount": 0 }, ........ ........ ], "odata.nextLink": "Order_Details?$skiptoken=10436,75" } |
Step-By-Step
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.
- Double click JSON Source and enter URL for OData API (You may use below example URL for demo)
1https://services.odata.org/V3/OData/OData.svc/Order_Details?$format=json
- Now configure Array Filter as below (you may see $.value[*] or some other node if URL is different)
- Click Preview data and OK to save
- Now click OK to save.
- Connect Your Source with target like ZS Trash destination and execute package. You will notice in the log that it paginates to return all rows
Fetch OData in SSIS (REST API Example)
Loading OData API into SQL Server / Other Target using SSIS
- Inside Data Flow, Drag and drop Upsert Destination Component from SSIS Toolbox
- Connect our Source component to Upsert Destination
- Double click Upsert Destination to configure it
- Select Target Connection or click NEW to create new connection
Configure SSIS Upsert Destination Connection - Loading data (REST / SOAP / JSON / XML /CSV) into SQL Server or other target using SSIS - 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
SSIS Upsert Destination - Columns Mappings - Click OK to Save Upsert 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.
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.
