{"id":9141,"date":"2020-09-25T17:35:17","date_gmt":"2020-09-25T17:35:17","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=9141"},"modified":"2025-09-25T21:02:07","modified_gmt":"2025-09-25T21:02:07","slug":"ssis-read-api-data-load-sql-table","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/ssis-read-api-data-load-sql-table\/","title":{"rendered":"How to Read API data in SSIS and Load into SQL Table"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>If you have need to integrate REST API in SSIS (i.e. fetch JSON, XML, CSV via HTTP call) and wondering how to Read API data in SSIS and Load into SQL DB (i.e. SQL Server, MySQL, Oracle) then you are at the right place.<\/p>\n

In this article we will cover simple step by step approach how to achieve API integration in few clicks. For detailed introduction please refer to our previous article<\/a>.<\/p>\n

In next few sections we will cover the following SSIS Components to read data from API like a Table (Parse in Rows \/ Column). Our instructions will be mostly for JSON Format but concepts are same for other formats (i.e. XML \/ CSV).<\/p>\n

\n\n\n\n<\/colgroup>\n\n\n\n\n
\"Custom<\/td>\nJSON Source Connector<\/a> (Read from REST API, JSON File or OData Service): Use this dataflow component when you have to fetch data from REST API webservice like a table<\/strong>. This component allows you to extract JSON data from webservice and de-normalize nested structure so you can save to Relational database such as SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local JSON files or direct JSON string (Wildcard pattern supported too e.g. c:\\data\\file*.json).<\/td>\n<\/tr>\n
\"Custom<\/td>\nXML\u00a0Source Connector<\/a> (SOAP, File, REST) : Use this dataflow component when you have to fetch data from XML or SOAP webservice and consume data like a table. This component allows you to extract data from webservice and save to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL).\u00a0This component also supports reading local XML files or direct XML string.<\/td>\n<\/tr>\n
\"Custom<\/td>\nCSV Source Connector<\/a> (File, REST) : Use this dataflow component when you have to fetch data from File or REST API which returns data in CSV format . This component allows you to extract data from webservice and save to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL).\u00a0This component also supports reading local CSV files or direct CSV string.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

There are few more API components not discussed in this articles. They are REST API Task<\/a> (no Parser – Just raw API calls) and Web API Destination<\/a>\u00a0(Write \/ Send Data To API \/ Delete rows). They both have different use case to check them see it fits in your need.<\/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

    Video Tutorial<\/h2>\n

    <\/div><\/div>
    <\/div>
    <\/div>
    <\/div><\/div><\/div>