Help > Tasks > Rest Api Task >
SSIS Rest Api Web Service Task
 PreviousNext

SSIS REST Api Task is your one stop solution to integrate REST Api Web Service Call in SSIS package. Innovative Drag and Drop approach will surely make your life easy.

Download SSIS PowerPack

Content

Video Tutorial


Step-By-Step

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). In this tutorial we are going to show you GET and POST method with Direct URL and using HTTP connection URL.
  1. First of all Download and Install SSIS PowerPack from here.
  2. Let's, Open Visual Studio and Create new SSIS package Project in BIDS/SSDT.
  3. From SSIS Toolbox look for items starting with “ZS”. Drag and Drop [ZS Rest API Task] to Designer Surface.
    SSIS REST Api Task - Drag and Drop

Lets, Call REST API in SSIS (Without any credentials) using Direct URL.

  1. Double click on REST API Task for configure it.
  2. Here, Select Direct URL in Access Mode and Enter Web URL to Call REST API, Select GET HTTP Request Method, Just check below Image steps.

    Example : Here, you can try sample URL to get Response from URL. 
    http://services.odata.org/V3/OData/OData.svc/Products?$format=json&src={{System::PackageName,URLENCODE}}
    SSIS REST Api Task - Get Method
  3. Click on Test Request / Response to see Response.
  4. Here, you can save response into Variable, Click here for check it Call REST API Task using HTTP Connection.
  5. Thats all, Just Run or Execute task.

Here, REST API call with Using HTTP Connection (Basic Authentication - HTTP connection).

  1. In this section we need to create HTTP Connection.
  2. Let's, Configure HTTP Request properties as below
  3. Right click on Connection Managers Panel to Create HTTP Connection, so you can use Source Path, and Context Menu will appear, Select New Connection from the Context Menu.
    SSIS REST Api Task - Create Connection
  4. Select ZS-HTTP Connection Manager from the Connection Managers list and Click on Add Button
    SSIS REST Api Task - Create Connection
  5. Now, in Connection Manager UI, Enter Web URl.for now We do not need Credentials type so select Not Set and You can select if you need it.
    SSIS REST Api Task - Create Connection
  6. Click Ok to save HTTP Connection settings UI.

Now lets, make REST API call with GET method using HTTP Connection.

  1. Double click on ZS REST API Task for configure it.
  2. While making HTTP Request you have to configure following items in your ZS REST API Task. Set GET in HTTP Request Method. Headers you can leave as it is if you do not need to pass Credentials.
    SSIS REST Api Task - Get Method
  3. Click on Test Request / Response to see Response.

Now lets, make REST API call with POST method using HTTP Connection.

  1. If you want to create Dynamic Token you can do it. just check out here.
  2. We can also make a call of API with OAuth 2.0 Authentication using REST API Task. Please refer to this link for the same click here.
  3. Moreover, Calling Web Service using POST Method, Pass Request Data, Custom Headers, Change HTTP Request Method to "POST". Body (Request Data) format must be like Body Content Type.

    Note: For JSON POST Data into JSON file use json format in Body Request Data and Select Body Content Type : JSON (application/json). 
    [{
"Cust":"AAA", "Cust":"BBB"
}]
    For more information you can check our blog for POST Method using ZS REST API Task.
    SSIS REST Api Task - Response Variable
  4. Click on Test Request / Response to POST data.

Saving REST API result into Variable or File.

  1. Lets save response into Variable. You can create New Variable while configure settings.
  2. Go to Response Settings Tab, Select Response content Type Json and Write Response Content Filter Expression. Check on Save Response Content. Select Save Mode to Variable and select it.
    SSIS REST Api Task - Response Variable
  3. Click Ok to save REST API Task settings UI.

You can change more setting like Error Handling, Validation, Advanced Settings using ZS REST API Task.

  1. You can run with any Error using Error Handling with ZS REST API Task. Check on Continue on error with specific response status code. You can Save Error status flag to Variable.
    SSIS REST Api Task - Handling Error with RESt API Request and Response - Continue on Error, Specific Message or Status Code
  2. Testing HTTP Web Service using GET Method, Pass Custom Headers by using below image few steps.
    SSIS REST Api Task - Validate Status Code, Content and Header
  3. For, SSL/TLS version, Compression, Timeout you can use Advanced settings Tab.
    SSIS REST Api Task - Advanced settings (SSL/TLS version, Compression, Timeout)
  4. Once you configure ZS REST API Task now you can connect source to target such as SQL Server, MySQL, Oracle, Flat File etc.
    SSIS REST Api Web Service Task - Advanced settings (Compression, SSL/TLS Protocol, Timeout

Properties

Property Name Description
RequestAccessMode Determines how to access Request URL

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
DirectUrl [0] Direct Url
UrlFromVariable [2] Url from variable
UrlFromConnection [3] Url from connection
RequestUrlVariable Variable name which holds request URL
SupportConnectionLessUrl
EnableStatusCheck Enable status check loop. When this option is enabled web requests are tried every N seconds to check desired value in response. Once desired value is found response is returned. Error is returned if failure indicator found or timeout occurs (i.e. Max iteration reached and still no success value found in the response).
StatusCheckMaxWaitSeconds Maximum wait time in seconds until we need to keep checking for success value or failure value indicator.
StatusFieldFilterType Extract type for success value / failed value field. For JSON Examples: $.status.value,  For Xml Examples: ./AuthInfo/Token/text()  --or-- ./Auth/@Token --or-- //*[local-name() = 'ResponseData']

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] None
Json [1] Json
Xml [2] Xml
Regex [3] Regex
StatusFieldFilterExpr
StatusCheckIterationWaitSeconds Wait time between each iteration for status check
StatusCheckForFailedValue Enable check to look for failure indicator. By default only SuccessValue is searched in response unless you set this option to true. Set StatusFailedValue to indicate failure regex / string pattern.
StatusSuccessValue Regex pattern or string value you like to search for Success indicator (e.g. Done|Success|Ok ). If you dont want to use Regex match then change StatusFieldFilterType.
StatusContinueValue Regex pattern or string value you like to search to continue status check (e.g. Done|Success|Ok ) If this value is found then status check is continue without checking for success or failure value. If you dont want to use Regex match then change StatusFieldFilterType.
StatusFailedValue Regex pattern or string value you like to search for Failure indicator (e.g. Cancelled|Aborted|Failed ). Set StatusCheckForFailedValue=true to use this value. If you dont want to use Regex match then change StatusFieldFilterType.
IsMultiPartUpload Set this option if you want to upload file(s) (i.e. POST RAW file data) or send data using Multi-Part encoding method (i.e. Content-Type: multipart/form-data). Multi-Part request allows you to mix key/value and upload files in same request. On the other hand raw upload allows only single file upload (without any key/value)

==== Raw Upload (Content-Type: application/octet-stream) =====  
To upload single file in raw mode check this option and specify full file path starting with @ sign in the Body (e.g.  @c:\data\myfile.zip )

==== Form-Data / Multipart Upload (Content-Type: multipart/form-data) =====  
To treat your Request data as multi part fields you must specify key/value pairs separated by new lines into RequestData field (i.e. Body). Each key value pair is entered on new-line and key/value are separated using equal sign (=). Preceding and trailing spaces are ignored also blank lines are ignored.
If field value has some any special character(s) then use escape sequence (e.g. For NewLine: \r\n, For Tab: \t, For at (@): \@). When value of any field starts with at sign (@) its automatically treated as File you want to upload. By default file content type is determined based on extension however you can supply content type manually for any field using this way [ YourFileFieldName.Content-Type=some-content-type ]. By default File Upload Field always includes Content-Type in the request (non file fields do not have content-type by default unless you supply manually). For some reason if you dont want to use Content-Type header in your request then supply blank Content-Type to exclude this header altogather [e.g. SomeFieldName.Content-Type= ]. In below example we have supplied Content-Type for file2 and SomeField1, all other fields are using default content-type.
See below Example of uploading multiple files along with additional fields. If some API requires you to pass Content-Type: multipart/form-data rather than multipart/form-data then manually set Request Header => Content-Type: multipart/mixed (it must starts with multipart/ else will be ignored).

file1=@c:\data\Myfile1.txt
file2=@c:\data\Myfile2.json
file2.Content-Type=application/json
SomeField1=aaaaaaa
SomeField1.Content-Type=text/plain
SomeField2=12345
SomeFieldWithNewLineAndTab=This is line1\r\nThis is line2\r\nThis is \ttab \ttab \ttab
SomeFieldStartingWithAtSign=\@MyTwitterHandle
AddMultiPartPostamble Some API server requires Multi-Part Requests to be ended by new line characters (i.e. \r\n). Enable this option if you face error such as - Unexpected end of MIME multipart stream. MIME multipart message is not complete -
MultiPartBoundary If your API requires custom boundary parameter then set this parameter. If you keep this blank then unique boundary is auto generated for each request (e.g. ---------------------------WebKitFormBoundarye0105838dcb14a098a9a3f355f7dc233). This option is ignored if you didn't set IsMultiPartUpload option to True
DisableAutoConvertMultiPartStream
TreatResponseAsMultiPart
PreventAutoRedirect By default HTTP Status 301, 302, 307 and 308 redirects automatically to new URL (Follows Location Header in Response). For some reason you want to disable it then set this option.
AllowAuthRedirectToAnyDomain By default when redirect happens (i.e. HTTP Code 301, 302, 307 and 308) to a different Host (domain or IP) then it removes the authorization header before redirecting to a new Host. If you trust the new host and you like to pass credentials after redirect then set this option. Without setting this option your HTTP request might fail.
RequestTimeoutMs HTTP request timeout in milliseconds. Enter 0 to use system default timeout.
SecurityProtocol Specifies which security protocol is supported for HTTPS communication. Using this option you can enable legacy protocol or enforce to use latest version of security protocol (Note: TLS 1.2 is only supported in SSIS 2014 or Higher).

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] System Default
Ssl3 [1] SSL v3.0
Ssl3Plus [2] SSL v3.0 or higher
Tls [3] TLS v1.0
TlsPlus [4] TLS v1.0 or higher
Tls11 [5] TLS v1.1
Tls11Plus [6] TLS v1.1 or higher
Tls12 [7] TLS v1.2
Tls12Plus [8] TLS v1.2 or higher
Tls13 [9] TLS v1.3
EnableCompressionSupport Enable support for gzip or deflate compression (for deflate you must turn on [Tls 1.0 Or Higher] Option on Advanced Settings - Security Protocol for HTTPS). When you check this option compressed response automatically de-compressed saving bandwidth. This option is only valid if web server supports compressed response stream. Check your API documentation for more information.
EnableRequestCompression Enable this option if you like to compress request body (i.e. Content-Encoding: gzip). Enabling this option can speedup data transfer especially when you are sending large amount data (i.e. Upload file)
EnableSplitStreamForUpload Enable Chunked Upload option for very large file (Only if API supports this)
BytesPerSplit Default Chunk Size in Bytes when EnableSplitStreamForUpload is turned on for large file upload. 0=Default value (32MB i.e. 32 * 1024 * 1024 Bytes)
ContentRangeHeader Content Range Header Name to send Bytes information (i.e. Range Counter). Default=Content-Range
ContentRangeValueTemplate Header value template to send Bytes information. You can use 3 placeholders anywhere in the template {next-range-start}, {next-range-end} and {total-bytes}. When you keep this value blank it uses default value. Default Value=bytes {next-range-start}-{next-range-end}/{total-bytes}
PartsResponseTemplate Template for combined responses of all parts. If this template is not defined then only last response is returned. You can use placeholder {all-responses} anywhere in the template. This is useful to gather part ids or other useful response information after each part is uploaded. All responses are joined using comma and new line.
AllowUnsecureSuite AllowUnsecureSuite
IgnoreCertificateErrors IgnoreCertificateErrors
RequestUrl Request URL where you want to submit HTTP Request
RequestUrlConnection HTTP Connection Manager for WebRequest to specified URL
RequestMethod Request method (e.g. GET, PUT, POST...)
RequestContentType Specifies content type for data you wish to POST. If you select Default option then system default content type will be used (i.e. application/x-www-form-urlencoded). If you specify Content-Type header along with this option then header value takes precedence.

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Default [0] Default
TextPlain [1] Text (text/plain)
ApplicationJson [2] JSON (application/json)
ApplicationXml [3] XML (application/xml)
TextXml [4] XML (text/xml)
TextXmlUtf8 [5] XML (text/xml;charset=UTF-8)
TextHtml [6] HTML (text/html)
ApplicationFormUrlencoded [7] Form (application/x-www-form-urlencoded)
ApplicationOctetStream [8] Binary (application/octet-stream)
Raw [9] Raw (No content-type)
MultiPartMixed [10] Multipart Mixed (multipart/mixed)
ApplicationGraphql [11] GraphQL (application/graphql)
OverrideConnectionUrl When this option is checked you can use Direct URL instead of using URL coming from selected HTTP connection manager (This option is only valid when RequestAccessMode = UrlFromConnection)
RequestHeaders Headers you want to send along with your request
RequestData Data you want to send along with request. This is not supported if Request Method is GET
SaveResponse Check this option if you want to save response to variable or file
SaveResponseStatusCode Check this option if you want to save response status code (numeric) to variable
ResponseAccessMode Determines how to save response content

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Variable [0] Save to Variable
File [1] Save to File
SaveAsBinary Treat response data as binary data rather than text data. Check this option when you downloading data which is not in text format or doesn't have encoding (e.g. zip, mp3, jpeg). This option is not available when you save response to Variable. When this option is checked ResponseCharset option is ignored.
ResponseDataFilePath File path where response content should be saved
ResponseHeaderMappings Http response header mappings
ResponseCookieMappings Cookie mapping. Use this setting to map cookie value to variable
CookieContainerVariable Cookie Container can be used to maintain state between multiple web requests. Example: You can login to site like wordpress and then extract any private page content by simply passing authentication cookies using this variable.
ConvertXmlToJson Convert XML Response to JSON. This setting is helpful to consume services which support XML format but not JSON. By converting XML to JSON you can then consume data using other components/tasks such as JSON Source, JSON Parser.
ResponseDataVariable Variable name where response content should be saved
ResponseStatusCodeVariable Variable name where you want to save response status code
ResponseContentType Response content type

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
None [0] None
Json [1] Json
Xml [2] Xml
Regex [3] Regex
ResponseContentFilter If you selected ResponseContentType=XML or JSON then use this property to filter Response Content using JSONPath or XPath expression.
ContinueOnResponseFilterError Continue on response filter error (i.e. Filter property referenced in expression (JSON/XML/RegX) not found in response)
ResponseCharset Specifies content encoding for response. Leave this blank to auto detect based on Content-Type header. When you specify this property then charset sent in response header is ignored (e.g. Content-Type: text/plain; charset=utf-8).
ValidateResponse If you want to validate for certain condition after response received check this option
ValidationRules Validation rules for response
ConsumeResponseOnError When error occurs no data is returned. Use this option to get content eventhough error occurs. When this option is checked you can't use [continue on error when specific string found in response] option
ContineOnAnyError Continue when any type of exception occurs during http request
ContineOnErrorForMessage Continue on error when specified substring found in response
ContineOnErrorForStatusCode Continue on error when specified status code returned from web server
ErrorStatusCodeToMatch Status code to match when error occurs and ContineOnErrorForStatusCode option is true. If Response status code matches to this code then task continues to run
ErrorStatusCodeToMatchRegex Status code(s) to match - separated by vertical bar (e.g. 404|405). When error occurs and ContineOnErrorForStatusCode option is true then if StatusCode matches to this code(s) then task continues to run
MatchForEqual Check for Equal or Not-Equal StatusCode when you set [ContineOnErrorForStatusCode] option
ErrorSubstringToMatch Error substring to match when error occurs and ContineOnErrorForMessage option is true. If Response status code matches to this code then task continues to run
ContineOn404Error Continue if response code 404 (Usually returned when specified Url is not found)
SaveErrorFlagToVariable Save Boolean flag into SSIS variable if error is detected  (Only valid if error handling options checked)
ErrorFlagVariableName Variable name where you want to store error flag (Only valid if error handling options checked)
LoggingMode

Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior).

Option Description
Normal [0] Normal
Medium [1] Medium
Detailed [2] Detailed
Debugging [3] Debugging
PrefixTimestamp When you enable this property it will prefix timestamp before Log messages.

Setting UI

SSIS REST Api Web Service Task - Settings UI
SSIS REST Api Web Service Task - Settings UI

See Also

References

Articles / Tutorials

Click here to see all articles for [SSIS REST API Task] category
How to read data from ElasticSearch using SSIS

How to read data from ElasticSearch using SSIS

Introduction In our previous article, we see how to load data into Elastic Search using SSIS. Now let’s look at how to read data from ElasticSearch using SSIS and load response into SQL Server. Elasticsearch is a powerful engine that allows you to store, aggregate and, most importantly, search data in a very analytical way. In this tutorial, you […]

Get data from Google Search Console API in SSIS and ODBC Apps

Get data from Google Search Console API in SSIS and ODBC Apps

Introduction In our previous few posts we saw how to call various google apis in SSIS.  In this post lets learn how to call Google Search Console API in SSIS or other ODBC Compatible Apps such as Power BI, Informatica, SSRS using API Drivers for ODBC About Google Search Console API (Google Webmaster API) If […]

How to refresh Power BI dataset with REST API using SSIS

How to refresh Power BI dataset with REST API using SSIS

Introduction In this article, you will learn how to refresh Power BI dataset with REST API using SSIS and ZappySys SSIS PowerPack. Power BI is a Microsoft reporting product that can get data from virtually any source and display it nicely in a report or a dashboard. Each Power BI report contains a dataset, which can be refreshed to show relevant […]

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 […]

Copyrights reserved. ZappySys LLC.