Script REST Web Service Request Transformation

Overview

Data flow transformation, executing HTTP request for each row in a data set.

---

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 .
• Body - the request body. Optional. Can be JSON, XML, plain text.
• Files - files to upload. Optional.
• Transform - the transformation function to be applied to the response. First parameter id the request response, second - object with the selected input parameters. Default implementation returns the response unchanged and don't use the input parameters.underscore.js functions are available.
• InputParameters - which input parameters to pass to the transformation function. Input parameters are not passed when the ResultType is "Binary".
• ResultType - the type of returned result. An output column of this type will be created.
  • Text = the column type is DT_WSTR.
  • Long Text = the column type is DT_NTEXT.
  • Binary = the column type is DT_IMAGE.
• TextLength - The length of output column when ResultType is "Text".
• ErrorRowDisposition - specifies how error rows are handled:
  • IgnoreFailure - Errors are ignored.
  • RedirectRow - Errors are redirected to 'Error Output'.
  • FailComponent - Errors cause component to fail.

---

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".

---

Parameterizing via input columns

The same parameters supporting variables also support input column values. When each row is processed the input columns references are replaced with their corresponding values.

Example: /userdata/[AccountName]. The [AccountName] is a input column reference. During runtime, this reference is replaced with the value of a column called "AccountName".

---

Using the result

The request result is written to a single output column. When the result is a JSON document, it can be processed further by using JSON Transformation reusable script.

---

Put input parameters in the result

Input parameters - Url, Method, Cookies, Headers, Query, Parameters, ContentType, Body, Files can be selected to be passed to the result transformation function. When the ResultType is not "Binary" and at least one parameter is selected, an object containing selected parameters is constructed and passed as second parameter to the function.

Usually the input parameters are put as properties of each element of the returned result array:

---

Processing in the control flow

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

---