{"id":1747,"date":"2017-08-30T17:06:31","date_gmt":"2017-08-30T17:06:31","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1747"},"modified":"2023-03-21T06:31:16","modified_gmt":"2023-03-21T06:31:16","slug":"loading-data-from-rest-api-to-sql-server-in-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/loading-data-from-rest-api-to-sql-server-in-ssis\/","title":{"rendered":"How to load JSON \/ REST API to SQL Server in SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>REST API is becoming the most popular way to communicate between multiple systems. In this blog post you will learn how to read data from JSON REST API<\/strong>\u00a0or JSON File<\/strong> and import API to SQL Server\u00a0Table\u00a0<\/strong>(or any other target e.g. Oracle, MySQL, Flat File). We will use drag and drop approach (yes no coding !!!)\u00a0 so in just few clicks you can extract data from API and load into SQL Table.<\/p>\n

For demo purpose we will read JSON format using\u00a0SSIS JSON \/ REST API Source<\/a>\u00a0but techniques listed in this article can be applied same way to read other formats such as XML or CSV. To read XML API or File use SSIS XML API \/ File Source<\/a>. If your API returns CSV format\u00a0 then use CSV API \/ File Source<\/a>. Again all these connectors can read from local file or REST API. Changing source path from URL to local file path will refresh the UI options.<\/p>\n

Prerequisites<\/span><\/h2>\n

Before we look into Step-By-Step section to read REST API data in SSIS let\u2019s make sure you met following requirements.<\/p>\n

    \n
  1. SSIS designer installed.\u00a0Sometimes it is referred as BIDS or SSDT (download it from Microsoft site<\/a>).<\/li>\n
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\n
  3. ZappySys SSIS PowerPack<\/em>\u00a0installed<\/a>. Click on the link to download FREE trial.<\/li>\n
  4. You have basic familiarity with REST API.<\/li>\n
  5. Obtain help file for API you trying to call. API reference usually contains very useful information about following things<\/li>\n<\/ol>\n

    REST API Concepts<\/h2>\n

    At high level calling REST API consist following things. You don’t have to understand all to get started but more you learn about each category is very useful.<\/p>\n

      \n
    1. Find out correct API URL to call your action\u00a0 (e.g. \/getSingleOrder,\u00a0 \/getAllOrders )<\/li>\n
    2. Request Type (e.g. GET \/ POST \/ PUT \/ DELETE )<\/li>\n
    3. Authentication \/ Security requirement (How to pass credentials)<\/li>\n
    4. Parameters (pass via URL \/ Body or Header)<\/li>\n
    5. HTTP Headers<\/li>\n
    6. Pagination (How to loop through all records<\/a> when response doesn’t include all records)<\/li>\n
    7. API Error Handling (Capturing error or continue on certain error)<\/li>\n
    8. API Limits (API call limit \/ Size restriction \/ API throttling )<\/li>\n<\/ol>\n

      API can be public or it may have some sort of authentication requirement. We have documented many API integration scenarios here<\/a> if you like to learn more.<\/p>\n

      Video Tutorial<\/h2>\n
      <\/div><\/div>
      <\/div>
      <\/div>
      <\/div><\/div><\/div>