{"id":39,"date":"2015-09-10T14:07:04","date_gmt":"2015-09-10T14:07:04","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=39"},"modified":"2025-10-02T19:16:52","modified_gmt":"2025-10-02T19:16:52","slug":"odata-paging-rest-api-paging-using-ssis-json-source","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/odata-paging-rest-api-paging-using-ssis-json-source\/","title":{"rendered":"How to read OData in SSIS – REST API Example"},"content":{"rendered":"

Introduction<\/h2>\n

In this article, we will learn how to utilize the SSIS JSON Source Component to retrieve JSON data from an OData service and perform OData pagination on significant results.\u00a0According to the OData specification, data can be presented in either JSON or XML format. For the XML format, refer to this article<\/a>.<\/span> This article primarily covers the OData JSON API example.<\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    REST API Pagination concept<\/h2>\n

    Most of the REST API Services limit the size of the dataset returned in a single request. When more data is found, it simply includes a pointer to the following result set. If you wish to fetch all data (e.g., loop through), you must check the following result set indicator. If it is not null, you can continue fetching until all data is retrieved. This article covers several pagination methods<\/span>.<\/p>\n

    Reading OData API in SSIS using JSON Source<\/h2>\n

    Let’s discuss how to read the OData API and Paginate Automatically. Below is a typical OData service Response (JSON Format). Notice how it includes the nextLink attribute, which points to the following URL to fetch more data. When you set Data\u00a0<\/strong>Format to OData<\/strong> in the JSON Source, it will handle<\/span>\u00a0pagination for you.<\/p>\n

    Request URL: <\/strong><\/p>\n

    https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Order_Details?$format=json<\/pre>\n
    Response:<\/b><\/p>\n
    {\r\n  \"odata.metadata\": \"https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/$metadata#Order_Details\",\r\n  \"value\": [\r\n    {\r\n      \"OrderID\": 10248,\r\n      \"ProductID\": 11,\r\n      \"Discount\": 0\r\n    },\r\n ........\r\n ........\r\n  ],\r\n  \"odata.nextLink\": \"Order_Details?$skiptoken=10436,75\"\r\n}\r\n<\/pre>\n
    Step-By-Step<\/h3>\n
    In this section, you will learn how to use the JSON Source Adapter to extract data from the OData API.<\/p>\n
      \n
    1. Firstly, you need to\u00a0download and install<\/strong>\u00a0SSIS\u00a0ZappySys PowerPack.<\/a><\/span><\/li>\n
    2. Once you have finished the first step, Open Visual Studio and create a New SSIS Package Project.<\/li>\n
    3. Now, Drag and Drop the SSIS\u00a0Data Flow Task<\/strong> from the SSIS Toolbox.\n
    4. Double-click on the Data Flow task to see the Data Flow designer surface.<\/li>\n
    5. From the SSIS toolbox, drag and drop the JSON Source onto the Data Flow Designer surface.\n
    6. Double-click JSON Source and enter URL for OData API (You may use the below example URL for demo)\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Order_Details?$format=json<\/code><\/pre>\n
       <\/li>\n
    7. Now configure Array Filter as below (you may see $.value[*]<\/strong> or some other node if URL is different)\n
    8. Click Preview data and OK to save\n
    9. Now click OK to save.<\/li>\n
    10. Connect Your Source with a target like ZS Trash destination and execute the package. You will notice in the log that it paginates to return all rows\n
      \"\"<\/a>
      Fetch OData in SSIS (REST API Example)<\/p><\/div><\/li>\n<\/ol>\n
      Loading OData API into SQL Server \/ Other Target using SSIS<\/h2>\n
      ZappySys SSIS PowerPack makes it easy to load data from various sources such as REST, SOAP, JSON, XML, CSV or from other source into SQL Server, or PostgreSQL, or Amazon Redshift, or other  targets. The Upsert Destination<\/strong> component allows you to automatically insert new records and update existing ones based on key columns. Below are the detailed steps to configure it.<\/p>\r\n
      Step 1: Add Upsert Destination to Data Flow<\/h3>\r\n
        \r\n
      1. Drag and drop the Upsert Destination<\/strong> component from the SSIS Toolbox.<\/li>\r\n
      2. Connect your source component (e.g., JSON \/ REST \/ Other Source) to the Upsert Destination.<\/li>\r\n<\/ol>\r\n
        \r\n\r\n\"\"<\/a>\r\n
        SSIS - Data Flow - Drang and Drop Upsert Destination Component<\/p>\r\n<\/div>\r\n
        Step 2: Configure Target Connection<\/h3>\r\n
          \r\n
        1. Double-click the Upsert Destination<\/strong> component to open the configuration window.<\/li>\r\n
        2. Under Connection<\/strong>, select an existing target connection or click NEW<\/strong> to create a new connection.\r\n