SSIS REST Api Web Service TaskSSIS REST API Web Service Task

SSIS REST API Web Service Task can be used to send/receive data using HTTP Web Request (e.g. GET, POST, PUT etc.). You can validate and save Web Response to file or variable. This task also supports Basic/OAuth 2.0 Authorization to connect with a verity of services (e.g. Facebook, Twitter, Salesforce).

Features

  • Call REST Style HTTP Web Service from SSIS (e.g. GET, POST, LIST) (Read more…)
  • Support for windows / basic authentication, client certificate and OAuth (Read more…)
  • Support for advanced error handling (Save error flag, continue on specific error or any error)
  • Support for File upload, REST API POST using Multipart/form-data (Read more…)
  • Download large files using binary option
  • Save response to variable or file
  • Support for Cookies  (Read more…)
  • Filter JSON/XML response using JSONPath or XPath expression (e.g extract Token property from the response)
  • Validate response for Response Code, Content or Header
  • Support for easy Test UI (see request/response)
  • Support for advanced options (Gzip compression, timeout, Set SSL/TLS protocols. TLS 1.2 supported)
  • Support for dynamic variable replacement with format specifiers anywhere inside header or request data (e.g. {{User::MyVar}} {{User::MyDate,yyy-MM-dd}} )
  • Use of BASE64ENC format specifiers (e.g. {{User::Creds,BASE64ENC}} to encode Authorization header.
  • Support for uploading very large files using Content-Range Header (Split Large Stream into chunks)
  • Allow to extract data from JSON array response using array item index in JSONPath (e.g. [0].some_field ) – on Response Tab (In Task / HTTP Connection- Dynamic Token)
  • Expose MatchForEqual Option for ContineOnErrorForStatusCode [Continue on error with specific response status code] on Error Handling Tab
  • Allow to set custom Boundary for Multipart/form-data Upload (Previously, we auto generate boundary for each request which may not be accepted by some API)
  • Support for SQL Server 2022, 2019, 2017, 2016, 2014, 2012 (32/64 bit) and now Azure Data Factory
If you have need to pull REST API data in table format then use JSON Source Connector instead. It allows you to extract data from webservice and save to SQL Server or any other target (Oracle, FlatFile, Excel, MySQL). JSON Source component also supports reading local JSON files or direct JSON string.
See also
Custom SSIS Components - JSON Source (File and REST API Connector) JSON Source Connector (File, REST API)
Custom SSIS Tasks - Export JSON File Task Export JSON File Task
Custom SSIS Tasks - JSON Parser Task JSON Parser Task
Custom SSIS Tasks - XML Source Connector (File, SOAP, REST API) XML Source Connector (File, SOAP, REST API)
Download Help File Buy
View All Tasks Like This

Featured Articles
ScreenshotsArticles / Useful LinksSystem Requirements

Sending data to your Web API using POST Method

REST API task example

SSIS REST API Task – Calling Web Service using POST Method, Pass Request Data, Custom Headers

Testing HTTP Request / Response

REST API task preview example

SSIS REST API Task – Testing HTTP Web Service using GET Method, Pass Custom Headers

Specify HTTP Response Settings (Save to File or Variable)

REST API save response in a variable

SSIS REST API Task – HTTP Response Save to File or Variable, Filter using XPATH or JSON PATH

API response validation

REST API validator tab

SSIS REST API Task – Testing HTTP Web Service using GET Method, Pass Custom Headers

Advanced options (support for TLS1.2)

Advanced tab in REST API task

SSIS REST API Task – Advanced settings (SSL/TLS version, Compression, Timeout)

SSIS Execution Log for REST API Task

REST API task package result

SSIS REST API Task – Call Web Service Execution Log

Articles


Click here to see all articles for [SSIS REST API Task] category


How to read YouTube API data in SSIS (Videos, Channels, Playlists)

How to read YouTube API data in SSIS (Videos, Channels, Playlists)

Introduction In last few articles we saw how to read data from various Google Services. In this article we will see how to read YouTube API data in SSIS. This blog mainly focus on SSIS approach but steps mentioned to call Google APIs can be useful for any developer regardless which programming language or toolset […]

 


Read Freshdesk data in SSIS – REST API Call

Read Freshdesk data in SSIS – REST API Call

Introduction In this post we will learn how to read Freshdesk data in SSIS using ZappySys JSON / REST API Source. We will see how to authenticate, paginate / parse and load Freshdesk data into SQL Server Table in just few clicks. If you don’t use Freshdesk and considering using Zendesk instead then check our previous […]

 


Understand HTTP Status Code and Fix Common Errors in REST API

Understand HTTP Status Code and Fix Common Errors in REST API

Introduction Any time you send HTTP Request according to HTTP Standard using tools like ZappySys REST API Drivers or SSIS Connectors you may receive web response along with HTTP Status Codes. Dealing with REST / SOAP API calls you may face errors and you may find it challenging to understand generic error message from server along with […]

 


Read / Write Shopify data in SSIS (REST API)

Read / Write Shopify data in SSIS (REST API)

Introduction In our previous article, we saw how to call REST API in SSIS. Now in let’s use that knowledge and learn how to read/write Shopify data in SSIS. If you are not aware of Shopify then its one of the most popular eCommerce platforms out there for small shops who sell online. Shopify provides […]

 


Read HTML Table in SSIS – Extract / Download Links / Images

Read HTML Table in SSIS – Extract / Download Links / Images

Introduction In this post you will learn how to extract data from web pages using SSIS. In other words, read HTML Table in SSIS, then Loop through extracted links and finally download files. To achieve this scenario we will use SSIS HTML Source and REST API Task.     Step-By-Step To achieve desired extraction from web pages […]

 


SSIS Magento data Read / Write using REST API Call

SSIS Magento data Read / Write using REST API Call

Introduction In this post we will lean SSIS Magento data read / write operations. Magento is a very popular eCommerce platform and they offer JSON based REST API and XML based SOAP API. You can use either API based on your need to automate common integration needs. We recommend using REST API (JSON API) if possible […]

 


How to read data from QuickBooks Online in SSIS

How to read data from QuickBooks Online in SSIS

Introduction QuickBooks Online is a well-known Cloud-based Accounting Software. In this post, you will learn how to implement QuickBooks Online API Integration with SQL Server or any other RDBMS (e.g. Oracle, MySQL, Postgresql) using SSIS in few clicks. We will use SSIS XML Source Connector to Read data from QuickBooks Online and Load into SQL Server / other targets (Using OAuth Connection). We […]

 


Read Salesforce Marketing Cloud data in SSIS (ExactTarget API)

Read Salesforce Marketing Cloud data in SSIS (ExactTarget API)

Introduction In this post we will learn how to call Salesforce Marketing Cloud API using SSIS and load into SQL Server. We will show you use case of SSIS REST API Task  and SSIS JSON Source Basically there are two steps to call Salesforce Marketing Cloud API Obtain ClientID and ClientSecret ( Check these steps ) Get Access Token by […]

 


Read Visual Studio Team Services data in SSIS (TFS Online) – Call REST API / Load to SQL Server

Read Visual Studio Team Services data in SSIS (TFS Online) – Call REST API / Load to SQL Server

Introduction In this blog, we will learn How to read Visual Studio Team Services data in SSIS and load into SQL Server Table, along with few other topics such as. How to register an OAuth App for Visual studio Team Service – TFS Online (Team Foundation Server) REST API Call How to read all Issues data […]

 


How to read Constant Contact data in SSIS – Call REST API / Load to SQL Server

How to read Constant Contact data in SSIS – Call REST API / Load to SQL Server

Introduction   In this blog, we will learn How to read Constant Contact data in SSIS and load into SQL Server Table, along with few other topics such as how to generate an API Token using Mashery Developer Account for Constant Contact REST API Call, how to read all Contacts from Constant Contact with SSIS and […]

 

See Also

Articles/Posts

Oracle Logo

Oracle CSV Export from Table or Query Data

Posted: June 10, 2015

Contents1 Introduction2 Download and Install ZappyShell Command Line Tools3 Download and Install Oracle Drivers3.1 Sample Oracle ODBC Connection String3.2 Sample Oracle OLEDB Connection String4 Oracle CSV Export – Single table5 Oracle CSV Export – Multiple tables6 Oracle CSV Export – Multiple tables (using pattern)7 Oracle CSV Export – SQL Query8 Oracle CSV Export – Split […]

SQl Server Logo

SQL Server Excel Export from Table or Query data

Posted: June 10, 2015

Contents1 Introduction2 The problem3 Solution3.1 Export Tables to CSV Using – ZappyShell Command Line Tools3.2 Export Tables to CSV Using – SSIS Export CSV File Task4 Download and Install ZappyShell Command Line Tools5 SQL Server Excel Export – Single table6 SQL Server Excel Export – Multiple tables7 SQL Server Excel Export – Multiple tables (using […]

SQl Server Logo

SQL Server JSON Export from Table or Query Data

Posted: June 10, 2015

Contents1 Introduction2 The problem3 Solution4 Export SQL Server data to JSON using SSIS PowerPack5 Export SQL Server data to JSON using ZappyShell Command line6 SQL Server JSON Export – Single table7 SQL Server JSON Export – Multiple tables8 SQL Server JSON Export – Multiple tables (using pattern)9 SQL Server JSON Export – SQL Query10 Using […]

SQl Server Logo

SQL Server CSV Export from Table or Query Data

Posted: June 10, 2015

Contents1 Introduction2 Download and Install ZappyShell Command Line Tools3 Download and Install SQL Server Drivers3.1 Sample ADO.net Connection String (No driver needed)3.2 Sample ODBC Connection String (SQL Native Client Driver)3.3 Sample OLEDB Connection String (SQL Native Client Driver)4 SQL Server CSV Export – Single table5 SQL Server CSV Export – Multiple tables6 SQL Server CSV […]

MySQL CSV Export from Table or Query result

Posted: June 5, 2015

Contents1 Introduction2 Download and Install MySQL ODBC Driver3 Download and Install ZappyShell Command Line Tools4 MySQL CSV Export – Single table5 MySQL CSV Export – Multiple tables6 MySQL CSV Export – Multiple tables (using pattern)7 MySQL CSV Export – SQL Query8 MySQL CSV Export – Split Files8.1 MySQL CSV Export – Split By Row Count8.2 […]

MySQL Excel Export from Table or Query result

Posted: June 5, 2015

Contents1 Introduction2 The problem3 Solution4 Download and Install MySQL ODBC Driver5 Download and Install ZappyShell Command Line Tools6 MySQL Excel Export – Single table7 MySQL Excel Export – Multiple tables8 MySQL Excel Export – Multiple tables (using pattern)9 MySQL Excel Export – SQL Query10 MySQL Excel Export – Split Files and Tabs10.1 MySQL Excel Export […]

MySQL JSON Export from Table or Query result

Posted: June 5, 2015

Contents1 Introduction2 The problem3 Solution4 Download and Install MySQL ODBC Driver5 Download and Install ZappyShell Command Line Tools6 MySQL JSON Export – Single table7 MySQL JSON Export – Multiple tables8 MySQL JSON Export – Multiple tables (using pattern)9 MySQL JSON Export – SQL Query10 Using Map File for nested MySQL JSON Export11 MySQL JSON Export […]

References

Click here to learn more about System Requirements
Download View All Tasks Like This