{"id":22,"date":"2015-08-03T04:41:10","date_gmt":"2015-08-03T04:41:10","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=22"},"modified":"2025-10-01T23:33:06","modified_gmt":"2025-10-01T23:33:06","slug":"call-rest-api-using-ssis-web-service-task","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/","title":{"rendered":"How to Call REST API in SSIS &#8211; Read JSON \/ XML \/ CSV"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-4254 alignleft\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg\" alt=\"Logo REST API\" width=\"122\" height=\"130\" \/><\/a>The REST API is becoming increasingly popular in the Data Integration landscape. <span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\">If you are an SSIS Developer (not a coder) and stumbled upon a question What is REST API and\u00a0<em>how to consume API inside an SSIS package?<\/em>&#8221; then you are at the right place :).<\/span> We encourage you to\u00a0read this article carefully and follow the various links we provided.<\/p>\n<p>In this article, you will learn about many REST API Tips \/ Tools on the topic\u00a0<em>of how to call a REST API using\u00a0<\/em><a target=\"_blank\" rel=\"noopener\"><em>the SSIS REST API Task<\/em><\/a><em>,\u00a0<\/em><a target=\"_blank\" rel=\"noopener\"><em>JSON Source,<\/em><\/a><em>\u00a0or\u00a0<\/em><a target=\"_blank\" rel=\"noopener\"><em>XML Source<\/em><\/a>. You will also learn how to perform\u00a0<em>HTTP GET\u00a0<\/em>and\u00a0<em>HTTP POST Requests<\/em> without needing to know any programming languages (e.g., C#, Java, Python) &#8211; simply do a drag-and-drop operation in SSIS.<\/p>\n<h2><\/h2>\n<h2>What is REST API \/ RESTful Web Service<\/h2>\n<p>Let&#8217;s first understand\u00a0<strong>what a REST API<\/strong> is, also\u00a0sometimes referred to as a\u00a0<strong>RESTful Web Service<\/strong>. This is a new buzzword that you will likely hear frequently. More and more services are available in the Cloud, which makes it obvious to come up with some way for you to access data in a more firewall-friendly manner. What can be a better option than accessing it using a several-decade-old HTTP protocol, so no additional configuration is required for anybody accessing Cloud Services?<\/p>\n<p>So, in short highlights for <em>REST Web Service<\/em><\/p>\n<ul>\n<li><em>REST Web Service<\/em> is a stateless client-server service model<\/li>\n<li>By passing an <strong>HTTP verb,<\/strong> you can perform server-side action over the standard HTTP protocol (e.g., GET, POST, LIST, DELETE, HEAD)<\/li>\n<li>You can pass <strong>parameters via URL<\/strong> query string and via <strong>HTTP Headers<\/strong><\/li>\n<li>If you are doing HTTP POST, then you can <strong>pass additional data in the Request Body<\/strong> along with the other two methods described above<\/li>\n<li><i>REST API<\/i> works by sending an HTTP Request and getting an <strong>HTTP Response<\/strong><\/li>\n<li>The HTTP Response of a web service can contain Headers and Response Data, which can be in binary, text, JSON, XML, or other formats.<\/li>\n<li>You can secure your data by simply sending it over the <strong>HTTPS (Secure HTTP) protocol<\/strong> or encrypting values passed along with your request.<\/li>\n<\/ul>\n<h2>Tasks\/Components in SSIS\u00a0for Consuming RESTful API \/ WebService<\/h2>\n<div class=\"su-table su-table-alternate\">\n<table>\n<colgroup>\n<col style=\"vertical-align: top\" width=\"100\" \/>\n<col style=\"vertical-align: top\" \/><\/colgroup>\n<tbody>\n<tr>\n<td><img decoding=\"async\" style=\"width: 72px;height: 72px\" src=\"\/\/zappysys.com\/images\/ssis-powerpack\/SSIS-Json-Source-Adapter.png\" alt=\"Custom SSIS Components - Json Source\" \/><\/td>\n<td style=\"vertical-align: top\"><a target=\"_blank\" rel=\"noopener\">JSON Source<\/a>\u00a0(Read from REST API, JSON file or OData Service): Use this dataflow component when you have to\u00a0<strong>fetch data from a REST API web service like a table<\/strong>.\u00a0This component enables you to extract JSON data from web services and de-normalize nested structures, allowing you to save the data to a Relational database, such as SQL Server, or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local JSON files or direct JavaScript strings (Wildcard pattern support, e.g., c:\\data\\file*.json).<\/td>\n<\/tr>\n<tr>\n<td><img decoding=\"async\" style=\"width: 72px;height: 72px\" src=\"\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/web-api-destination\/ssis-web-api-destination.png\" alt=\"SSIS Custom Target Adapter - Web API Destination\" \/><\/td>\n<td style=\"vertical-align: top\"><a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-web-api-destination-connector\/\">Web API Destination<\/a> (POST data to API URL): Use this data flow component when you need to load data into a target system via API calls (<a href=\"https:\/\/zappysys.com\/blog\/http-post-in-ssis-send-data-to-web-api-url-json-xml\/\" target=\"_blank\" rel=\"noopener\">POST to URL<\/a>). E.g., Loading Contacts into a Marketing System like <a href=\"\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/\" target=\"_blank\" rel=\"noopener\">MailChimp<\/a> for email campaigns. Loading documents into CRM or document storage systems, e.g,. Couchbase or <a href=\"\/\/zappysys.com\/blog\/call-elasticsearch-api-using-ssis\/\" target=\"_blank\" rel=\"noopener\">ElasticSearch<\/a>.<\/td>\n<\/tr>\n<tr>\n<td><img decoding=\"async\" style=\"width: 72px;height: 72px\" src=\"\/\/zappysys.com\/images\/ssis-powerpack\/ssis-rest-api-web-service-task.png\" alt=\"Custom SSIS Tasks - SSIS Rest Api Web Service Task\" \/><\/td>\n<td style=\"vertical-align: top\"><a target=\"_blank\" rel=\"noopener\">REST API Tak<\/a>: Use this task when you don\u2019t want to pull REST API data in tabular format but want to call the REST API for POST data to the server, DELETE data from the server, or things like download an HTML page, extract Authentication tokens, etc, where you are not necessarily dealing with data in tabular format. This task also allows you many other options, such as saving the RAW response into a variable or a file.<\/td>\n<\/tr>\n<tr>\n<td><img decoding=\"async\" style=\"width: 72px;height: 72px\" src=\"\/\/zappysys.com\/images\/ssis-powerpack\/ssis-xml-source.png\" alt=\"Custom SSIS Components - XML Source (Read File\/SOAP\/REST Web Service)\" \/><\/td>\n<td style=\"vertical-align: top\"><a target=\"_blank\" rel=\"noopener\">XML Source<\/a> (SOAP, File, REST): Use this dataflow component when you have to fetch data from an XML or SOAP web service and consume data like a table. This component allows you to extract data from a web service and save it to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local XML files or a direct XML string.<\/td>\n<\/tr>\n<tr>\n<td><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" style=\"width: 72px;height: 72px\" src=\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/images\/csv-source\/ssis-csv-file-source.png\" alt=\"Custom SSIS Components - CSV Source (Read File\/REST API)\" width=\"128\" height=\"128\" \/><\/td>\n<td style=\"vertical-align: top\"><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-csv-file-source-flat-file-web-api\/\">CSV Source<\/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 a web service and save it to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). This component also supports reading local CSV files or direct CSV string.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h2>Using SSIS JSON Source to read from REST API and load into SQL Server<\/h2>\n<div>\n<p>If you need to consume a REST API Service and store the result into SQL Server or any other RDBMS\/FLAT File, then you can check this\u00a0<a target=\"_blank\" rel=\"noopener\">SSIS JSON Source<\/a>. The JSON Source can deserialize your nested JSON (it also supports JSONPath filter expressions).<\/p>\n<table>\n<tbody>\n<tr>\n<td><div class=\"lyte-wrapper fourthree\" style=\"width:480px;max-width:100%;margin:5px auto;\"><div class=\"lyMe\" id=\"WYL_aO8cVRyCBWw\"><div id=\"lyte_aO8cVRyCBWw\" data-src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=%2F%2Fi.ytimg.com%2Fvi%2FaO8cVRyCBWw%2Fhqdefault.jpg\" class=\"pL\"><div class=\"tC\"><div class=\"tT\"><\/div><\/div><div class=\"play\"><\/div><div class=\"ctrl\"><div class=\"Lctrl\"><\/div><div class=\"Rctrl\"><\/div><\/div><\/div><noscript><a href=\"https:\/\/youtu.be\/aO8cVRyCBWw\" rel=\"nofollow\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=https%3A%2F%2Fi.ytimg.com%2Fvi%2FaO8cVRyCBWw%2F0.jpg\" alt=\"\" width=\"480\" height=\"340\" \/><br \/>Watch this video on YouTube<\/a><\/noscript><\/div><\/div><div class=\"lL\" style=\"max-width:100%;width:480px;margin:5px auto;\"><\/div><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h2>REST API\u00a0Task &#8211; Ad-hoc web requests &#8211; Call REST API (POST, DELETE)<\/h2>\n<table>\n<tbody>\n<tr>\n<td><div class=\"lyte-wrapper fourthree\" style=\"width:480px;max-width:100%;margin:5px auto;\"><div class=\"lyMe\" id=\"WYL_jPdcQlWOBZA\"><div id=\"lyte_jPdcQlWOBZA\" data-src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=%2F%2Fi.ytimg.com%2Fvi%2FjPdcQlWOBZA%2Fhqdefault.jpg\" class=\"pL\"><div class=\"tC\"><div class=\"tT\"><\/div><\/div><div class=\"play\"><\/div><div class=\"ctrl\"><div class=\"Lctrl\"><\/div><div class=\"Rctrl\"><\/div><\/div><\/div><noscript><a href=\"https:\/\/youtu.be\/jPdcQlWOBZA\" rel=\"nofollow\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/plugins\/wp-youtube-lyte\/lyteCache.php?origThumbUrl=https%3A%2F%2Fi.ytimg.com%2Fvi%2FjPdcQlWOBZA%2F0.jpg\" alt=\"\" width=\"480\" height=\"340\" \/><br \/>Watch this video on YouTube<\/a><\/noscript><\/div><\/div><div class=\"lL\" style=\"max-width:100%;width:480px;margin:5px auto;\"><\/div><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>HTTP GET Request using SSIS Web Service Task or JSON\/XML Source<\/h2>\n<div>Calling the REST API from the\u00a0<strong>SQL Server BI Stack<\/strong> usually requires programming skills.\u00a0<span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\">However, in this section, we will learn how easy it is to call\u00a0<strong>a RESTful Web Service<\/strong> using the SSIS Web Service Task, JSON Source, or XML Source (all of which are drag-and-drop components<\/span>).<\/div>\n<p>In this example, we will retrieve JSON data from an OData web service.<\/p>\n<ul>\n<li><a title=\"Download - SSIS REST API Web Service Task\" href=\"\/\/zappysys.com\/products\/ssis-powerpack\/download\/\">Download and Install SSIS PowerPack<\/a><\/li>\n<li>Create a new sample SSIS package in BIDS\/SSDT<\/li>\n<li>From the SSIS Toolbox, look for items starting with &#8220;ZS&#8221;. Drag and Drop [ZS Rest API Web Service Task] to Designer Surface<\/li>\n<li>Configure HTTP Request properties as below<\/li>\n<li>Configure HTTP Response properties as below<\/li>\n<li>Run SSIS Package<\/li>\n<\/ul>\n<h3>Pass values in HTTP Headers<\/h3>\n<div>\n<p>You can pass a value in HTTP Headers using a direct approach or a dynamic approach. If you wish to pass a value from an SSIS variable, then use a variable placeholder, e.g., {{User::varSomeVariable}}. You can also use a variable format specifier, e.g.<\/p>\n<ul>\n<li><strong>Format Date:<\/strong> {{User::varSomeVariable,yyyy-MM-dd}}<\/li>\n<li><strong>Encode into BASE64:<\/strong> {{User::varSomeVariable,BASE64ENC}}<\/li>\n<li><strong>Decode into plain text from BASE64:<\/strong> {{User::varSomeVariable,BASE64DEC}}<\/li>\n<\/ul>\n<\/div>\n<div>\n<div id=\"attachment_11523\" style=\"width: 848px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-POST-SSIS-Call-Web-Service.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11523\" class=\"size-full wp-image-11523\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-POST-SSIS-Call-Web-Service.png\" alt=\"\" width=\"838\" height=\"735\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-POST-SSIS-Call-Web-Service.png 838w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-POST-SSIS-Call-Web-Service-300x263.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-POST-SSIS-Call-Web-Service-768x674.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><p id=\"caption-attachment-11523\" class=\"wp-caption-text\">Calling REST API in SSIS using REST API Task, Pass headers, Body, Url Parameters<\/p><\/div>\n<div class=\"mceTemp\"><\/div>\n<\/div>\n<div>\n<div id=\"attachment_11522\" style=\"width: 848px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-GET-Test-SSIS-Web-Service-Call-Pass-Custom-Header.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11522\" class=\"size-full wp-image-11522\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-GET-Test-SSIS-Web-Service-Call-Pass-Custom-Header.png\" alt=\"\" width=\"838\" height=\"735\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-GET-Test-SSIS-Web-Service-Call-Pass-Custom-Header.png 838w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-GET-Test-SSIS-Web-Service-Call-Pass-Custom-Header-300x263.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-HTTP-GET-Test-SSIS-Web-Service-Call-Pass-Custom-Header-768x674.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><p id=\"caption-attachment-11522\" class=\"wp-caption-text\">SSIS REST Api Task &#8211; HTTP GET, Test SSIS Web Service Call, Pass Custom Header<\/p><\/div>\n<\/div>\n<div>\n<div class=\"mceTemp\"><\/div>\n<div id=\"attachment_11524\" style=\"width: 848px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-Validate-Status-Code-Content-and-Header.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11524\" class=\"size-full wp-image-11524\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-Validate-Status-Code-Content-and-Header.png\" alt=\"\" width=\"838\" height=\"735\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-Validate-Status-Code-Content-and-Header.png 838w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-Validate-Status-Code-Content-and-Header-300x263.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/SSIS-REST-Api-Task-Validate-Status-Code-Content-and-Header-768x674.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><p id=\"caption-attachment-11524\" class=\"wp-caption-text\">REST API response validation ( By status code, content, header)<\/p><\/div>\n<\/div>\n<div><\/div>\n<h3>Pass values in the Query String dynamically<\/h3>\n<div>Most web services usually accept parameters via the query string. You can also use variable placeholders to make your URL with Querystring dynamic (e.g., https:\/\/mysite.com\/orderservice\/?startrow={{User::varStart}}&amp;endrow={{User::varEnd}}<\/div>\n<h2>HTTP\u00a0POST Request using SSIS Web Service Task or JSON\/XML Source<\/h2>\n<div>\n<p>When you select the HTTP POST\u00a0<span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\">method, the content-type is set to\u00a0<em>application\/x-www-form-urlencoded by default<\/em><\/span>. With the POST method, you can send POST data. When the POST method is selected, the data textbox becomes editable. You can also use variable place holders in POST data to make it dynamic.<\/p>\n<p>POST data is usually in key\/value format (e.g., user=abcd&amp;pass=mypass123), but sometimes the service requires data in JSON format or XML format. In that case, you can submit that way. Depending on the service requirements, you must set additional headers to indicate the content type (please refer to your service documentation).<\/p>\n<h3>Uploading files using HTTP\u00a0Multipart\/form-data POST Request<\/h3>\n<p>Many API support uploading files along with your POST request. Refer to <a href=\"\/\/zappysys.com\/blog\/rest-api-file-upload-using-ssis-multi-part-post\/\" target=\"_blank\" rel=\"noopener\">this article<\/a> to learn more about how to upload files using REST API calls.<\/p>\n<\/div>\n<h2>Passing Credentials to your Web Service<\/h2>\n<div>\n<p>If your service requires authentication, ensure that you pass the credentials needed in the correct format. Below is a list of some of the most common authentication techniques.<\/p>\n<p>There are mainly three connections:<\/p>\n<ol>\n<li><a href=\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/ssis-http-connection-manager.htm\" target=\"_blank\" rel=\"noopener\">HTTP Connection Manager<\/a><\/li>\n<li><a href=\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/ssis-oauth-connection-manager.htm\" target=\"_blank\" rel=\"noopener\">OAuth Connection Manager<\/a><\/li>\n<li>Salesforce Connection<\/li>\n<\/ol>\n<h3><strong>Using HTTP Connection<\/strong><\/h3>\n<p>HTTP Connection supports multiple methods for authenticating to your API. Here are some popular modes.<\/p>\n<ul>\n<li><a href=\"\/\/zappysys.com\/blog\/how-to-set-base64-encoded-authorization-header-for-http-web-request\/\" target=\"_blank\" rel=\"noopener\">Basic Authentication<\/a> (UserID and Password Approach)<\/li>\n<li><a href=\"https:\/\/zappysys.com\/blog\/call-soap-rest-api-using-dynamic-token-ssis\/\" target=\"_blank\" rel=\"noopener\">Dynamic Token approach<\/a> (Two-step process &#8211; First get Token and then Call API (Pass the token extracted in the first step)<\/li>\n<li>Static Token<\/li>\n<li><a href=\"https:\/\/zappysys.com\/blog\/using-hmac-authentication-api-ssis-odbc-drivers\/\" target=\"_blank\" rel=\"noopener\">HMAC \/ Hash Signature Authentication<\/a><\/li>\n<li><a href=\"https:\/\/zappysys.com\/blog\/get-data-from-workday-in-ssis-using-soap-or-rest-api\/\" target=\"_blank\" rel=\"noopener\">SOAP WSS Security<\/a><\/li>\n<li>Windows Authentication (NTLM)<\/li>\n<li>Client Certificate<\/li>\n<\/ul>\n<h3>OAuth Connection<\/h3>\n<p>Many modern APIs now support OAuth Standard (i.e. v2.0, v1.0, v1.0a). ZappySys offers out-of-the-box support for this standard using <a href=\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/ssis-oauth-connection-manager.htm\" target=\"_blank\" rel=\"noopener\">OAuth Connection Manager<\/a><\/p>\n<ul>\n<li><a href=\"https:\/\/zappysys.com\/blog\/rest-api-authentication-with-oauth-2-0-using-ssis\/\" target=\"_blank\" rel=\"noopener\">OAuth authorization<\/a>\n<ul>\n<li>Authorize Code Grant (3-legged &#8211; using Login Popup)<\/li>\n<li>Client Credentials Grant (Simple key\/secret &#8211; No login Popup)<\/li>\n<li>Password Grant (Use of userid \/password rather than only key\/secret)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/div>\n<h3>Basic Authentication &#8211; Set Authorization Header (Base64 Encoding)<\/h3>\n<div>The HTTP Standard supports BASIC Authorization mode, in which you can pass the username and password as a base64-encoded string. Check this article for more <span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\">information\u00a0<a target=\"_blank\" rel=\"noopener\">on how<\/a><\/span><a href=\"\/\/zappysys.com\/blog\/how-to-set-base64-encoded-authorization-header-for-http-web-request\/\"> to pass a BASE64 Encoded Authorization Header<\/a><\/div>\n<h3>Token-based approach &#8211; such as OAuth<\/h3>\n<div>This is becoming the most common approach, utilizing protocols such as OAuth, where the user first authenticates to the service using an AccountKey and SecretKey. Once authenticated, you receive a token that can be valid for a certain duration (or an infinite duration). After you receive the token, you can call services (each service call will include this token). Parsing a token from the initial response can be achieved through the REST API Response Filter Expression (e.g., $.token) or using the <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-parser-task-free\/\">JSON Parser Task<\/a>.<\/div>\n<div>\n<h3>OAuth Authorization<\/h3>\n<div>All REST API Tasks\/Components in Zappysys SSIS PowerPack support <strong>OAuth<\/strong> Authorization. OAuth is gaining popularity, and many larger companies have already adopted this standard (e.g., Facebook, Twitter, Google, Salesforce). With this method, you can connect to the REST API Service without storing your User ID\/Password. Only the first time, you need to log in using your credentials to obtain an initial Token, and after that, the service will continue to use the Access Token rather than your username and password. Once the Token expires, it can automatically renew.\u00a0 <a href=\"\/\/zappysys.com\/blog\/tag\/oauth\/\">Check this for more information about using OAuth<\/a>.<\/div>\n<\/div>\n<h3>Windows Authentication (NTLM)<\/h3>\n<p>By default, ZappySys REST API Tasks\/Components use your default credentials. You can also use the HTTP Connection Manager with the &#8220;Use Credentials&#8221;&gt; &#8220;Use Windows Authentication&#8221; option. <a href=\"\/\/zappysys.com\/blog\/how-to-set-base64-encoded-authorization-header-for-http-web-request\/\" target=\"_blank\" rel=\"noopener\">See this article<\/a><\/p>\n<h3>Pass Client Certificate<\/h3>\n<p>Sometimes you may have to pass a client certificate along with your web request. The server checks the certificate passed along with your request and, if it matches, proceeds with your request. Here is the real-world use case <a href=\"https:\/\/azure.microsoft.com\/en-us\/documentation\/articles\/cloud-services-certs-create\/\" target=\"_blank\" rel=\"noopener\">How Azure Management Api uses Client Certificate<\/a>.<\/p>\n<p><span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\">ZappySys\u00a0<a target=\"_blank\" rel=\"noopener\">HTTP Connection Manager<\/a>\u00a0and\u00a0<a target=\"_blank\" rel=\"noopener\">OAuth Connection Manager<\/a> both support passing a Client certificate.<\/span><\/p>\n<h2>Saving HTTP Web Service Response to a File or Variable<\/h2>\n<div class=\"d-editor-preview\">\n<p>Let\u2019s break down how to save the response from a ZappySys REST API Task into either an SSIS variable or a file within your SSIS package.<\/p>\n<p><strong>Scenario:<\/strong> Imagine you\u2019re using the ZappySys REST API Task to retrieve JSON data from a URL. You want to store this data for further processing within your SSIS package.<\/p>\n<h3>1. Saving the REST API Response to an SSIS Variable<\/h3>\n<p>This is ideal when you need to manipulate the data within your SSIS package.<\/p>\n<ol>\n<li><strong>Add a ZappySys REST API Task to your Control Flow:<\/strong><br \/>\nDrag and drop the task onto your SSIS package\u2019s Control Flow.<\/li>\n<li><strong>Configure the REST API Task:<\/strong>\n<ul>\n<li>Set the <code>Connection<\/code> Property to your REST API Connection Manager. (Optional)<\/li>\n<li>Specify the <code>URL<\/code> of the API endpoint.<\/li>\n<li>Configure any necessary headers, query parameters, or request body.<\/li>\n<li>Go to the &#8220;Response Settings&#8221; tab.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Configure the Output Variable:<\/strong>\n<ul>\n<li>Check-mark Save Response content<\/li>\n<li>Select Save Mode as &#8220;Save to Variable&#8221;<\/li>\n<li>Select or create an SSIS variable of type <code>String<\/code>. This variable, named <code>User::WeatherResponse<\/code> will store the REST API response.<\/li>\n<\/ul>\n<div class=\"mceTemp\"><\/div>\n<div id=\"attachment_11521\" style=\"width: 848px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-an-SSIS-Variable.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11521\" class=\"size-full wp-image-11521\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-an-SSIS-Variable.png\" alt=\"\" width=\"838\" height=\"735\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-an-SSIS-Variable.png 838w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-an-SSIS-Variable-300x263.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-an-SSIS-Variable-768x674.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><p id=\"caption-attachment-11521\" class=\"wp-caption-text\">Saving the REST API Response to an SSIS Variable<\/p><\/div><\/li>\n<\/ol>\n<h3>2. Saving the REST API Response to a File<\/h3>\n<p>This is useful for archiving the response or when you need to process the data outside of SSIS.<\/p>\n<ol>\n<li><strong>Add a ZappySys REST API Task to your Control Flow:<\/strong><br \/>\nDrag and drop the task onto your SSIS package\u2019s Control Flow.<\/li>\n<li><strong>Configure the REST API Task:<\/strong>\n<ul>\n<li>Set the <code>Connection<\/code> Property to your REST API Connection Manager. (Optional)<\/li>\n<li>Specify the <code>URL<\/code> of the API endpoint.<\/li>\n<li>Configure any necessary headers, query parameters, or request body.<\/li>\n<li>Go to the &#8220;Response Settings&#8221; tab.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Configure the Output File:<\/strong>\n<ul>\n<li>Check-mark Save Response content<\/li>\n<li>Select Save Mode as &#8220;Save to File&#8221;<\/li>\n<li>In the \u201cEnter File Path\u201d property, specify the path and filename where you want to save the REST API response. For example, if you are fetching JSON data from a product API, you can save it to the following file: <code>C:\\Data\\Products.json<\/code>.\n<div class=\"mceTemp\"><\/div>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<div id=\"attachment_11520\" style=\"width: 848px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-a-File.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11520\" class=\"size-full wp-image-11520\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-a-File.png\" alt=\"\" width=\"838\" height=\"735\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-a-File.png 838w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-a-File-300x263.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/Saving-the-REST-API-Response-to-a-File-768x674.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><p id=\"caption-attachment-11520\" class=\"wp-caption-text\">Saving the REST API Response to a File<\/p><\/div>\n<p>REST APIs enable array filtering to extract specific data from JSON or XML responses. This is achieved through techniques such as query parameters, JSONPath (for JSON), and XPath (for XML), allowing for the precise selection of elements. Additionally, regular expressions can be employed for string-based filtering and data validation.<\/p>\n<div id=\"attachment_11519\" style=\"width: 848px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-APIs-enable-array-filtering-to-extract-specific-data-from-JSON-or-XML-responses.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11519\" class=\"size-full wp-image-11519\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-APIs-enable-array-filtering-to-extract-specific-data-from-JSON-or-XML-responses.png\" alt=\"\" width=\"838\" height=\"275\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-APIs-enable-array-filtering-to-extract-specific-data-from-JSON-or-XML-responses.png 838w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-APIs-enable-array-filtering-to-extract-specific-data-from-JSON-or-XML-responses-300x98.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-APIs-enable-array-filtering-to-extract-specific-data-from-JSON-or-XML-responses-768x252.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><p id=\"caption-attachment-11519\" class=\"wp-caption-text\">REST APIs enable array filtering to extract specific data from JSON or XML responses.<\/p><\/div>\n<h4>Key Setting: \u201cTreat response as binary\u201d<\/h4>\n<p>As highlighted in the steps, the <strong>\u201cTreat response as binary\u201d<\/strong> option in the \u201cResponse\u201d tab is the most important setting for correctly saving binary files. If you don\u2019t check this, the task might try to interpret the binary data as text.<\/p>\n<p><strong>Example Scenario:<\/strong><\/p>\n<p>Let\u2019s say you want to download a ZIP file from a REST API or a Public URL:<\/p>\n<ul>\n<li><strong>API URL:<\/strong> <code>https:\/\/example.com\/api\/download\/archive.zip<\/code><\/li>\n<li><strong>File Path:<\/strong> <code>C:\\Downloads\\archive.zip<\/code><\/li>\n<li>In the ZS Rest API Task\u2019s \u201cResponse\u201d tab, you would:\n<ul>\n<li>Check-mark Save Response content<\/li>\n<li>Select Save Mode as &#8220;Save to File&#8221;<\/li>\n<li><strong>Check \u201cTreat response as binary\u201d.<\/strong><\/li>\n<li>In the \u201cEnter File Path\u201d property, specify the path and filename where you want to save the REST API response. <code>C:\\Downloads\\archive.zip<\/code>.<\/li>\n<\/ul>\n<div class=\"mceTemp\"><\/div>\n<div id=\"attachment_11518\" style=\"width: 848px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-API-Task-Download-File.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11518\" class=\"size-full wp-image-11518\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-API-Task-Download-File.png\" alt=\"\" width=\"838\" height=\"280\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-API-Task-Download-File.png 838w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-API-Task-Download-File-300x100.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2015\/08\/REST-API-Task-Download-File-768x257.png 768w\" sizes=\"(max-width: 838px) 100vw, 838px\" \/><\/a><p id=\"caption-attachment-11518\" class=\"wp-caption-text\">REST API Task &#8211; Download File<\/p><\/div><\/li>\n<\/ul>\n<p>By following these steps, you can effectively use the ZappySys Rest API Task to download and save binary content from REST APIs to files within your SSIS workflows.<\/p>\n<\/div>\n<h2>Save HTTP Web Response Headers \/ StatusCode<\/h2>\n<div>If you wish to save the response to a file, then go to the response tab of the REST API Task and check the &#8220;Save Response&#8221; option, and you can select Save to File option from the dropdown<\/div>\n<h2>HTTP Web Response Validation<\/h2>\n<p><a target=\"_blank\" rel=\"noopener\">REST API Task<\/a> supports validating your response for a specific header, status code, or content value.\u00a0See the Validation tab for more information. Using this feature, you can throw an error if a certain header is missing from the response or reject the response if the StatusCode is other than 200.<\/p>\n<div>\n<h2>REST API Pagination (Loop through multiple requests)<\/h2>\n<p>Another popular concept in REST API is paging. If your response is significant, the server often returns a partial response. You must then ensure that you consume the remaining data by requesting all subsequent URLs until the last page is returned.<\/p>\n<p><a href=\"\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/\" target=\"_blank\" rel=\"noopener\">Read this article to learn more about rest api paging<\/a><\/p>\n<h2>HTTP Web Response Error Handling<\/h2>\n<p><a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-rest-api-web-service-task\/\">REST API Task<\/a> supports rich error handling. You can ignore specific errors and save the error flag into a variable based on any of the following criteria.<\/p>\n<ul>\n<li>Continue on any error<\/li>\n<li>Continue on error with specific Response Code (e.g. 404)<\/li>\n<li>Continue on error with a specific string in the message<\/li>\n<\/ul>\n<h2>Read\/Write and Parse Cookies for HTTP Web Request\/Response<\/h2>\n<p>Many times, you need to supply an authentication token via cookies, or any other information must be passed via cookies. In such cases, the REST API Task provides complete support for reading, writing, or parsing individual values from\u00a0a cookie string. Look at the Cookies tab for more information. The Cookie Mapping grid can help you to map a cookie value to an SSIS variable (Write Cookie value to a variable).<\/p>\n<p><a href=\"\/\/zappysys.com\/blog\/ssis-rest-api-task-pass-cookies-to-web-request\/\" target=\"_blank\" rel=\"noopener\">Read this article to learn more about passing cookies with your web request<\/a><\/p>\n<h2>Changing Headers\/URL or POST data Dynamically<\/h2>\n<p>Most of the fields on the REST API Task, JSON Source, and XML Source support placeholders.<\/p>\n<p>Use a placeholder anywhere in the following fields using {{User::yourVariable}} format. If you edit the value, you may see the Insert PlaceHolder option.<\/p>\n<ul>\n<li>Url<\/li>\n<li>Body<\/li>\n<li>Headers<\/li>\n<\/ul>\n<p>You may format datetime using special syntax like this {{User::myDate,yyyy-MM-dd HHmmss.fff}}<\/p>\n<\/div>\n<h2>Extract a single value from JSON\/XML Web Response (e.g., Token)<\/h2>\n<div>If your web response is in JSON or XML format, then you can filter it using Expression (e.g.,\u00a0<a target=\"_blank\" rel=\"noopener\">JSONPath\u00a0<\/a>or\u00a0<a href=\"https:\/\/en.wikipedia.org\/wiki\/XPath\" target=\"_blank\" rel=\"noopener\">XPath<\/a>). Go to the Response Tab and select Content Type Format from the dropdown. Enter an expression to select a nested sub-document or a value from your response. If you want to save individual properties from the response into multiple variables, then use <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-parser-task-free\/\">JSON Parser Task<\/a><\/div>\n<p>The most common use case is if you are getting a token from your JSON REST API service. Assume that Service sends you a response in the following format, but you only care about the access_token property from the response below. In that case you can use JSON Path expression like this <strong>$.data.access_token<\/strong><\/p>\n<pre>{\r\n\u00a0\u00a0\u00a0 data: {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 access_token: 111223344444,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 expires_in: 3600\r\n\u00a0\u00a0\u00a0 }\r\n}<\/pre>\n<h2>REST API Testing \/ Debugging Tools (3rd party)<\/h2>\n<p>Now, let&#8217;s look at a few popular tools that you will need to test\/debug REST API calls during your development cycle.<\/p>\n<h3>Testing Rest API<\/h3>\n<p>There are a\u00a0 few ways to test REST APIs, and each tool has its pros and cons, but the most popular tool to test REST API is Postman, so we will start from there<\/p>\n<ol>\n<li><a href=\"https:\/\/www.postman.com\/downloads\/\" target=\"_blank\" rel=\"noopener\">Postman<\/a> &#8211; GUI<br \/>\nPostman offers an excellent User interface to test the API. You can edit URLs, methods, and parameters, and add authentication with ease. See our article on how to <a href=\"https:\/\/zappysys.com\/blog\/postman-to-ssis-odbc-powerpack\/\" target=\"_blank\" rel=\"noopener\">migrate Postman settings to zappysys UI<\/a>. Many API Providers offer easy-to-use Postman settings files for Testing API in Postman. If your API offers such a file, simply import it into Postman and start testing before using the ZappySys UI.<\/li>\n<li><a href=\"https:\/\/www.soapui.org\/downloads\/latest-release.html\" target=\"_blank\" rel=\"noopener\">SoapUI<\/a>\u00a0&#8211; GUI<br \/>\nSoapUI is another tool to test SOAP or REST APIs. They are primarily known for SOAP API Testing, but in recent versions, they have enhanced REST API Testing capability.<\/li>\n<li><a href=\"https:\/\/curl.haxx.se\/\" target=\"_blank\" rel=\"noopener\">cURL<\/a> &#8211; Command line<br \/>\nIf you want to call\/test the API via the command line, then this is the most popular tool. Many API vendors use cURL syntax in their API documentation. Here is how to <a href=\"https:\/\/zappysys.com\/blog\/using-ssis-curl-syntax-to-ssis-api-task\/\">migrate cURL syntax to ZappySys UI<\/a>.<\/li>\n<li>Fiddler- GUI<br \/>\nFiddler has limited capability to test API call &#8211; <a href=\"https:\/\/zappysys.com\/blog\/how-to-use-fiddler-to-analyze-http-web-requests\/#How_to_Test_API_using_Fiddler_Call_REST_API_for_without_Postman\" target=\"_blank\" rel=\"noopener\">See Replay in Composer option<\/a><\/li>\n<li><a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-rest-api-web-service-task\/\" target=\"_blank\" rel=\"noopener\">ZappySys REST API Task<\/a><br \/>\nYou can use the ZappySys REST API Task to\u00a0<a href=\"https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2019\/04\/ssis-call-freshdesk-rest-api.png?ssl=1\" target=\"_blank\" rel=\"noopener\">test the API inside Visual Studio<\/a>. Edit URL \/ Headers \/ Body, etc, and click the <strong>Test Request Response<\/strong> option<\/li>\n<\/ol>\n<h3>Debugging REST API &#8211; Proxy<\/h3>\n<p>So far, we have seen client tools to test Request\/Response for the URL you entered in the UI, but what if you want to Sniff Requests sent by some applications for which you have no way to know what Request Data \/ URL, or Body is sent behind the scenes? What response is it getting back and so on? Well, not to worry&#8230; Telerik Fiddler is the most popular tool at your fingertips and it&#8217;s FREE.<\/p>\n<ol>\n<li><a href=\"https:\/\/zappysys.com\/blog\/how-to-use-fiddler-to-analyze-http-web-requests\/\" target=\"_blank\" rel=\"noopener\">Fiddler<\/a>\u00a0&#8211; HTTP \/\u00a0 HTTPS Proxy \/ Debugger<\/li>\n<li><a href=\"https:\/\/www.wireshark.org\/\">Wireshark<\/a> &#8211; Low-level Network Packet Sniffer<\/li>\n<\/ol>\n<h2>Conclusion<\/h2>\n<p>The REST API is becoming increasingly popular every day. With that, Data integration from RESTful services is going to be a challenge.\u00a0<span style=\"box-sizing: border-box; margin: 0px; padding: 0px;\"><a target=\"_blank\" rel=\"noopener\">The SSIS REST API Task\u00a0<\/a>and\u00a0<a target=\"_blank\" rel=\"noopener\">SSIS JSON Source<\/a> can help resolve some of the issues you may encounter<\/span> with SSIS. Use JSON Source if you need to store data into a SQL Table. Use REST API Task if you need to make an ad-hoc HTTP\/REST request (such as get token, Delete Record, etc). ZappySys REST API Components gives you total control over your REST API Integration challenges without learning a programming language (e.g., Python, Ruby, C#, Java).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction The REST API is becoming increasingly popular in the Data Integration landscape. If you are an SSIS Developer (not a coder) and stumbled upon a question What is REST API and\u00a0how to consume API inside an SSIS package?&#8221; then you are at the right place :). We encourage you to\u00a0read this article carefully and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":4254,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[106,14,157,6,13,60,26,27,3,104,4,5,7],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>How to Call REST API in SSIS - Read JSON \/ XML \/ CSV | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Using SSIS Web Service Task Call REST API (HTTP Web Service) and do HTTP POST or HTTP GET. If Response is XML or JSON then you can filter and save content.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to Call REST API in SSIS - Read JSON \/ XML \/ CSV | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Using SSIS Web Service Task Call REST API (HTTP Web Service) and do HTTP POST or HTTP GET. If Response is XML or JSON then you can filter and save content.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2015-08-03T04:41:10+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-10-01T23:33:06+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg\" \/>\r\n\t<meta property=\"og:image:width\" content=\"217\" \/>\r\n\t<meta property=\"og:image:height\" content=\"232\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"17 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/\",\"url\":\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/\",\"name\":\"How to Call REST API in SSIS - Read JSON \/ XML \/ CSV | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg\",\"datePublished\":\"2015-08-03T04:41:10+00:00\",\"dateModified\":\"2025-10-01T23:33:06+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Using SSIS Web Service Task Call REST API (HTTP Web Service) and do HTTP POST or HTTP GET. If Response is XML or JSON then you can filter and save content.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg\",\"width\":217,\"height\":232,\"caption\":\"Logo REST API\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Call REST API in SSIS &#8211; Read JSON \/ XML \/ CSV\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Call REST API in SSIS - Read JSON \/ XML \/ CSV | ZappySys Blog","description":"Using SSIS Web Service Task Call REST API (HTTP Web Service) and do HTTP POST or HTTP GET. If Response is XML or JSON then you can filter and save content.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/","og_locale":"en_US","og_type":"article","og_title":"How to Call REST API in SSIS - Read JSON \/ XML \/ CSV | ZappySys Blog","og_description":"Using SSIS Web Service Task Call REST API (HTTP Web Service) and do HTTP POST or HTTP GET. If Response is XML or JSON then you can filter and save content.","og_url":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2015-08-03T04:41:10+00:00","article_modified_time":"2025-10-01T23:33:06+00:00","og_image":[{"width":217,"height":232,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg","type":"image\/jpeg"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"17 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/","url":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/","name":"How to Call REST API in SSIS - Read JSON \/ XML \/ CSV | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg","datePublished":"2015-08-03T04:41:10+00:00","dateModified":"2025-10-01T23:33:06+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Using SSIS Web Service Task Call REST API (HTTP Web Service) and do HTTP POST or HTTP GET. If Response is XML or JSON then you can filter and save content.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/06\/REST-API-icon.jpg","width":217,"height":232,"caption":"Logo REST API"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/call-rest-api-using-ssis-web-service-task\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to Call REST API in SSIS &#8211; Read JSON \/ XML \/ CSV"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/22"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=22"}],"version-history":[{"count":26,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/22\/revisions"}],"predecessor-version":[{"id":11525,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/22\/revisions\/11525"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/4254"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=22"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=22"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=22"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}