REST

Overview

REST Connection Manager is an SSIS Connection Manager for establishing REST connections. Use the API to build components based on the REST Connection Manager. The REST Connection Manager depends on configuration which describes the REST service in use.

Overview

The REST Source Component is an SSIS Data Flow Component for consuming data from REST services.

Overview

The REST Destination Component is an SSIS Data Flow Component for inserting data into a REST resource.

A REST configuration is an XML file with a .RCM extension (short for REST Connection Manager) that describes a Web API. It stores all settings related to consuming a Web API in SSIS context - authentication, resources, endpoints, data sending and processing.

By supporting JavaScript snippets/expressions in XML attributes, a REST configuration enables handling any special requirements. As a result, the REST Execution Engine can easily support APIs that are not fully standards compliant (e.g. implementing a custom authentication).

The main elements of a RCM configuration include:

• Service - specify the base service URL (i.e. base address for API endpoints) and configure general error handling settings.
• Authentication - describe any authentication methods that the service supports. You can define User parameters, which can be configured in the REST Connection Manager editor, Token - builtin functionality of REST engine that process and store OAuth token information.
• Resources - describe templates, resources and endpoints.
  • Templates fields are fields that can be referenced by multiple resources.
  • Template resources enable specifying common settings for multiple resources.
  • A resource contains all fields that any of its related endpoint returns or accepts.
  • There can be multiple endpoints that return particular resource data and also there can be zero or one endpoint corresponding to the different ways resource data can be modified (i.e. creating, updating, upserting and deleting).
• Script - contains custom JavaScript code to be referenced in some XML attributes. The scripts can be organized in modules for logical grouping (e.g. the code related to automating a particular authentication method can be isolated in its own module).

An XML Schema is available for documenting and validating the RCM format.

NOTE: Resources can be automatically imported from OpenAPI specification.

The REST Configuration Editor allows authoring custom REST configurations. Its main functionality includes:

• Assisted editing of all major elements of a REST configuration (authentications, resources, fields, endpoints, scripts)
• Expert mode for manual editing of the XML representation of each element of a REST configuration
• Assisted editing of template expressions (allowing for a mix of JavaScript code snippets and plain text, see details)
• Quick testing of endpoints configuration (see details)
• Context-sensitive help (via "Help" button or "F1")
• Wizard-like navigation with breadcrumbs. Tip: Quick navigation to the previous page is available via "Alt + Backspace" or "Alt + LeftArrow" shortcuts (similar to web browsing)

Wizard Pages Reference

Start Page
  Authentications List Page
   Authentication Page
    Token Page
     Token Authorization Handling Page
     Refresh Token Page
  Template Fields Page
  Resources List Page
   Resource Page
    Resource Fields Page
     Initialize Resource Fields from JSON
     Field Page
      Get Field Page
      Set Field Page
    Read Endpoints List Page
     Read Endpoint Page
      Results Paging Page
    Action Endpoint Page
  Scripts Page
   Script Module Page
  Error Handling Settings Page

Common wizard pages:
Request Parameters List Page
  Request Parameter Page
User-specified Parameters List Page
  User-specified Parameter Page

Common popup forms, invoked from the wizard:
Template Expression Editor
HTTP Request/Response Tester

Start Page

The starting point of the wizard provides a summary of the configured elements.

• Clicking the "XML" button allows viewing/editing of the underlying XML representation of the configuration (see also the XML Schema reference)
• Clicking the "OpenAPI" button allows importing resource from an OpenAPI (a.k.a. Swagger) file (more details here).
• Clicking the "X" button allows resetting the configuration and starting from scratch.
• Clicking the "Share" button allows sharing your custom configuration with us, in case you would like to contribute to the COZYROC SSIS+ community

Authentications List Page

Displays the list of authentications (usually just one). Allows creating a new authentication from a template. The available templates are: AWS Signature 2.0, Basic Authentication, Google Service Account 2.0, Google User Account 2.0, OAuth 1 and OAuth 2.

A demonstration video related to OAuth authentication configuration can be reviewed here.

A demonstration video related to Google Service authentication configuration can be reviewed here.

Tip: For OAuth-based authentications the settings that need to be configured after choosing its template can be found in the corresponding Script module (accessible via the Authentication Page).

Authentication Page

Displays the authentication settings.

For further information see the XML Schema reference.

Token Page

Displays the settings how to handle the sign-on request and the processing of the response during the token retrieval. The token authorization handling is configured in another page (see below).

Tip: Some simpler authentication methods like Basic Authentication don't require configuring settings for token retrieval.

For further information see the XML Schema reference.

Token Authorization Handling Page

Displays the settings how to handle the token authorization workflow.

Tip: Some services don't work with the default callback https://www.cozyroc.com/oauth_callback, and require setting up a Local HTTP Listener (e.g. http://localhost/).

For further information see the XML Schema reference.

Refresh Token Page

Displays the refresh token settings - the request parameterization, extraction of the refresh token from the response and the refresh token expiration.

For further information see the XML Schema reference.

Request Parameters List Page

Displays the list of automatically processed parameters for constructing an HTTP request.

For further information see the XML Schema reference.

Request Parameter Page

Displays the definition of an automatically processed parameter for constructing an HTTP request.

For further information see the XML Schema reference.

User-specified Parameters List Page

Displays the list of user-specified parameters (most often in the context of constructing an HTTP request).

For further information see the XML Schema reference.

User-specified Parameter Page

Displays the definition of an user-specified parameter (most often in the context of constructing an HTTP request).

Tip: When in the context of endpoint parameterization, if a parameter is not explicitly referenced somewhere in a template expression (e.g. like parameters.myparam or parameters.get("myparam")), then it should be specified as "Enumerable" to True . This extra setting controls whether the values, specified in the REST Source/Destination Editor would be added to the list of the request parameters (only available for user-specified parameters in endpoints).

For further information see the XML Schema reference.

Template Fields Page

Displays the list of template fields.

For further information see the XML Schema reference.

Resources List Page

Displays the list of resources (template or normal resources).

Tip Template resources are like "base classes" - they are useful for defining in a single place some common parameters for the resources that reference them.

For further information see the XML Schema reference.

Resource Page

Displays the resource settings: name, an optional base resource (a.k.a. "Template"), CRUD endpoints, etc.

For further information see the XML Schema reference.

Resource Fields Page

Displays the resource fields. Besides the grid with the resource fields, there is an optional base field (aka "Component Field Template"), which allows the resource to inherit all child fields from a specified template field.

• "View JSON Sample" opens a form to inspect a sample JSON record with the fields of the resource.
• "Init from JSON" opens a form to quickly create the resource fields from a sample JSON.
• "New Field" opens the page for configuring a child field.

For further information see the XML Schema reference.

Initialize Resource Fields from JSON

Specify a JSON and click "Preview" to see what fields would be created or updated in the resource. To accept the changes click "OK". To cancel, click "Cancel".

Field Page

Displays the field configuration.

For the purpose of sending (via REST Source) and receiving data (via REST Destination), a field can be one of the following types:
* Simple field, that is to be mapped to a column in a data flow. It needs to have specified "Data type" and optional "Length" (either directly or via a referenced template field).
* Flattened Compound field (an object or an array) that is processed as a simple string field. It needs to have the "Composite" checkbox checked and to have specified column length big enough to store the corresponding JSON values.
* Compound field that is processed in a separate SSIS input/output (for more information see Composite Records). It can be either an array (i.e. checked "Array" checkbox) or an object (i.e. with specified child fields).

Tip For custom processing of fields in endpoints, see Get or Set handling.

For further information see the XML Schema reference.

Get Field Page

Displays the configuration for specifying a custom behavior when reading field value from an endpoint response. A demonstration video can be reviewed here.

For further information see the XML Schema reference.

Set Field Page

Displays the configuration for specifying a custom behavior when calculating the field value when preparing an endpoint request.

For further information see the XML Schema reference.

Read Endpoints List Page

Displays the list of endpoints for fetching records of the resource.

Tip: There can be multiple endpoints that fetch records with the same structure (i.e. the structure, defined by the resource fields). If more than one, you need to specify a unique identified for each endpoint and an optional display name, which would appear in the REST Source UI.

Read Endpoint Page

Displays the settings for an endpoint that reads data. A demonstration video related to the result extraction can be reviewed here.

Tip: Use the "Test" button for testing the related HTTP request and response processing.

For further information see the XML Schema reference.

Results Paging Page

Displays the settings for specifying the logic for iterating through pages of endpoint results. A demonstration video can be reviewed here.

Tip: Use the button next to the "Next Value" expression to quickly choose from common paging patterns (e.g. "Cursor-based paging", "Fixed size pages", etc.).

For further information see the XML Schema reference.

Action Endpoint Page

Displays the settings for an endpoint that writes/changes data (i.e. Create/Update/Delete/Upsert). A demonstration video can be reviewed here.

Tip: Use the "Test" button for testing the related HTTP request and response processing.

For further information see the XML Schema reference for Create, Update, Delete and Upsert.

Scripts Page

Displays the list of JavaScript script modules. There can be multiple independent modules (e.g. an authentication can have its own JavaScript imports and variables in a separate module).

For further information see the XML Schema reference.

Script Module Page

Displays the name of the script module and and its JavaScript code.

For further information see the XML Schema reference.

Error Handling Settings Page

Displays the error handling settings:

• Status - determines whether the response indicates success or an error (Example {{=!response.hasOwnProperty('ok') || response.ok}}
• Errors List - How to retrieve the list of errors from the response, in order to get the message and code of the first error, if many (Example: {{=response.error}})
• Error Code - How to retrieve the error code from an analyzed item (Example:"{{=item.code}}")
• Error Message - How to retrieve the error message from an analyzed item (Example:"{{=item.error}}")
• Success Message - How to retrieve the error message from an analyzed item (Example:"{{=item.status}}")

For further information see the XML Schema reference.

Template Expression Editor

Many elements of COZYROC REST configurations support template expressions for implementing custom processing logic with the help of JavaScript code. Those elements have "..." buttons next to them, clicking on which opens the Template Expression Editor.

The Template Expression Editor provides assistance in constructing a valid template expression. A template expression allows mixing text and dynamic expressions via the following syntax: This {{= obj.method() }} and this {{= obj.method() }} again, where This is plain text and obj.method() is a JavaScript expression, enclosed within{{= and }}. If e.g.obj.method() returns "result", then the expression would be evaluated as "This result and this result again".

The editor supports:

• Advanced syntax highlighting - assists in identifying plain text, various elements of JavaScript code and available variables.
• List of available variables in the corresponding context (e.g. the connection variable is available everywhere, while response only when processing the contents of an HTTP response. There is a documentation how to use them on the right. Adding a variable to an expression is done by double-clicking or using drag&drop.

HTTP Request/Response Tester

The popup form for previewing HTTP request & response is accessible from the pages for endpoints. It enables quick testing of the endpoint configuration without sending any data over the wire. This is especially useful when testing non-trivial JavaScript-based template expressions. It supports:

• Previewing the HTTP request - after optionally specifying user-defined parameters and an input record (the latter is applicable only for "write" endpoints).
• Previewing the handling of HTTP response - after configuring the expected HTTP response.

Overview

These public interfaces are used in REST components. They are provided for implementing advanced functionality in your scripts. In order for you to use these interfaces in your project, you have to reference CozyRoc.SSISPlus.dll and RestSharp.dll libraries. Also include CozyRoc.SqlServer.SSIS, CozyRoc.Rest and RestSharp namespaces.

IConnection

When you call REST Connection Manager AcquireConnection method, it returns object implementing IConnection interface. This is the interface used for interaction with REST service.

Methods

Properties

IField

This interface is used to retrieve REST resource field information.

Properties

IResource

This interface is used for REST resource management.

Methods

IStatus

This interface is used for reporting back status.

Methods