Script REST Web Service Request Task

Overview

Script task to call a web API endpoint.

Setup

The script has the following parameters:

• RestConnection - optional REST connection for specifying authentication method, base URL, proxy and other settings.
• Method - the request method. Can be GET, POST, PUT, DELETE, HEAD, OPTIONS or PATCH.
• Url - the request URL. Required. Can be relative when RestConnection is specified.
• Cookies - the request cookies. Optional. Syntax is [name]=[value]. Each item is on a new line. Alternatively, a Cookie header can be specified directly in the Headers parameter.
• Headers - the request headers. Optional. Syntax is [name]:[value]. Multiple headers are separated with a newline.
• Query - the request query string parameters. Optional. Syntax is [name]=[value]. Multiple query string parameters are separated with newline.
• Parameters - the request body parameters. Optional. Syntax is [name]=[value]. Multiple body parameters are separated with newline. When used the header Content-Type becomes application/x-www-form-urlencoded.
• ContentType - the Media type of the body of the request. For example: text/html, application/json, application/xml, etc .
• IsBodyFile - defines if body is composed from a file.
  • Body - the request body. Optional. Can be JSON, XML, plain text.
  • BodyFile - a file to use as a request body. Optional.
• Transform - the transformation function to be applied to the response. By default it returns the response unchanged.underscore.js functions are available by default.
• IsResultFile - defines if result is written in a file.
  • ResultVariable - the package variable where to store the transformed result. Optional.
  • ResultFile - the file to store result. Optional.
• Retry - defines if retry will be made.
  • Condition - the condition to evaluate against returned response. underscore.js functions are available by default.
  • Pause - the time in seconds to pause between each retry.
  • MaxRetries - the maximum number of retries before the task fails.

Parameterizing via SSIS variables

Most of the parameters ( Url, Cookies, Headers, Query, Parameters, Body, Transform ) support variable references. During runtime, all references to SSIS variables are substituted with their corresponding values.

Example: /@[User::SubAccount]/response/@[User::Mailing_Id]/optouts". The @[User::SubAccount] is a variable reference. During runtime, this reference is replaced with the value of a variable called "User::SubAccount".

Using the result

The request result is written to a single variable. When the result is a JSON document, it can be processed further by using JSON to Variables Task reusable script.

Processing in the data flow

Use REST Web Service Request Transformation reusable script to execute REST web service requests in the data flow.