
COZYROC REST components, part of COZYROC SSIS+ suite since 2016, are third-party plug-ins for Microsoft SSIS that make it easy to consume any Web API. The connectivity to REST APIs is implemented thru an XML configuration file which can contain both statically defined elements and also JavaScript custom code for additional flexibility. By utilizing this unique and powerful framework we are able to very quickly provide support for many other REST-based applications. This framework can also be utilized by our customers to create their own connectivity to REST-based applications for which there is no pre-built configuration available yet. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for REST. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
The COZYROC SSIS+ library also includes the powerful and elegant Data Flow Task Plus, not found in any other competitive product on the market. Data Flow Task Plus allows creation of dynamic data flows at runtime and can be used with COZYROC's REST adapters. You can create processes where the addition or deletion of REST resource fields doesn't necessitate modification of existing SSIS packages. This powerful feature requires no programming skills to use and greatly simplifies your maintenance efforts.
The REST integration package consists of four parts:
- REST Connection Manager
- Stores the credentials and functionality for connecting to REST service.
- Support for any authentication based on parameters or token.
- Support for web service call retry, including ability to retry only in specific situations.
- Support for web service calls per seconds throttling.
- Well documented API for use by user-defined scripts.
- REST Source component
- Retrieves data from a REST resource.
- The source can be dynamically modified at runtime using an SSIS expression.
- Support for resource metadata extension (XML definition) for additional customization.
- Natural support for reading composite objects (i.e. line items) where a separate output is configured for each composite object.
- REST Destination component
- Inserts or updates data in REST resource.
- Supports of up to four resource actions: Create, Update, Delete or Upsert
- Provides information about records with errors as well as the identifier assigned for new records created.
- Support for resource metadata extension (XML definition) for additional customization.
- Natural support for writing composite objects (i.e. line items) where a separate input is configured for each composite object.
- Reusable scripts
- Unique technology which allows user-defined reusable scripts for additional REST integration scenarios.
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.
Quick Start
In this article, you will learn how to find the documentation for the REST application you want to connect to, how to get an API Key for the REST application you want to connect to, and how to ensure you are using a version of COZYROC's SSIS+ that has the ability to define a custom REST configuration.
- Look for the API documentation for the application you wish to connect to by searching for the following: “[application name] Developer API”. In this example, we searched for “Copper Developer API”.
- Go to the Authentication section of the Developer API documentation. For Copper, that is https://developer.copper.com/introduction/authentication.html .
- Follow the instructions for how to generate an API token or an API key. Both terminologies seem to be used interchangeably.
In this article, you will learn how to begin the configuration of a connection manager for a custom REST application.
This segment shows how to define the parameters that will be exposed to the user. In the case of Copper, they will enter their email address and API key.
- Click on the “New User Parameter” button in the bottom right corner again.
- For Name, type in “APIKey”.
- Set Type to “password” so that it is not visible when the user pastes their API key into this parameter.
- Now you are done configuring the APIKey parameter; it will be known as {{=connection.user.APIKey}}.
In this segment, you will learn how to define the parameters that are sent to Copper during authentication. The user parameters defined in the previous segment (ie. Email and APIKey) will provide the values to be used in two of these parameters. The third parameter is a static parameter.
- Copy and paste the name of the static parameter that Copper requires into the Name field (X-PW-Application) of the New Parameter editor.
- Go back to the Copper documentation page and copy the value to be used for this parameter (developer_api).
- Paste this value into the Value field of the New Parameter editor.
- For Type, choose “HttpHeader”.
- You’re done configuring the static parameter.
- Start the Fiddler Tracing tool so you can see the traffic when you test your configuration. This article describes how to install Fiddler and how to trace traffic.
In this section you will be shown how to test that your definitions for the resources and fields are correct. We will use the REST Source component to view data from the application.
Parameters
Advanced
Use the Advanced page of the REST Connection Manager dialog to specify additional properties for logging, service requests throttling and optional error retry.
Specify the verbose log file name for investigating REST connection issues.
Specify the number of web services requests per second. The default is 5 requests/second.
Specify the maximum request retry number.
Specify the number of seconds to pause between retries. The default is 30 seconds.
Optional. Specify to retry only if the error message matches the RegEx pattern.
Proxy
Use the Proxy page of the REST Connection Manager dialog to specify properties if your server is behind firewall.
Specify the name or IP address of the proxy server. If not specified, a proxy server is not used.
Specify the port number on the proxy server to use for the connection.
Specify the user name to access the proxy server.
Specify the password to access the proxy server.
Knowledge Base
- Why the buttons on the REST Connection manager are missing?
- What is an experimental REST configuration and where can I find the documentation for the experimental REST configurations?
- I see a response returned from the web service, but the preview in REST Source does not show any data.
- How to dynamically set a user parameter in a REST Connection.
- How to implement paging in the Custom REST Configuration Editor.
Related scripts
What's New
- Breaking Change:
Resource.Field.Default
attribute has been removed from the configuration schema. - New: Enable retrieving API tokens within a modern Chromium-based web browser component (as an alternative to the IE-based one).
- New: OpenAPI import.
- New: Authentication template for Google User Account.
- New: Authentication template for Google Service Account.
- New: Authentication template for Amazon Web Services Signature v4.
- New: Iterator template for OData services.
- Fixed: Disabled the default .NET forward-slash (/) and dot (.) unescape because it breaks connectivity to resources with such characters in use.
- Fixed: Custom -> 'Init from JSON' action didn't create a proper composite element definition.
- New: Ability to author configurations to arbitrary REST services.
- New: Slight modifications in the RCM configuration format for improved consistency.
- New: Getting Help now opens a more relevant documentation page (depending on the selected configuration and current tab).
- New: Included option to tweak user agent of web browser used in 'New Token' wizard dialog.
- New: Introduced connection.
Overview
The REST Source Component is an SSIS Data Flow Component for consuming data from REST services.
Parameters
General
Use the General page of the REST Source dialog to specify the source REST resource.
Select the COZYROC REST connection manager.
Related Topics: REST Connection Manager
Select endpoint.
Select a REST resource as the source.
Specify the number of rows to be retrieved as a batch. Default is 200.
Specify additional custom metadata.
Specify one or more filters for the data retrieval. A grid with filters help is displayed under the filter's entry grid.
Specify the maximum number of records to retrieve.
Columns
Use the Columns page to specify which columns are to be processed from the source REST resource and to specify new names for the columns being processed, if desired.
This table lists the columns, by name, that are available to be processed from the source REST resource. If there are columns that you do not want to process, uncheck them from the list.
Shows the columns that will be processed from the source REST resource based on the columns selected in the Available External Columns list.
This shows the column name that will be used during processing. The name of any column may be changed by typing over the text in this list. Each name must be unique.
Error Output
Use the Error Output page to select error handling options for the columns.
View the name of the data source.
View the selected source column.
Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.
Related Topics: Handling Errors in Data
Specify what should happen when a truncation occurs: ignore the failure, redirect the row, or fail the component.
View a description of the error.
Knowledge Base
- Error Message: Unexpected keyword '' at position
- Where can I find the documentation for the REST Source component?
- I see a response returned from the web service, but the preview in REST Source does not show any data.
- How to convert a date into a usable date string that takes into account the timezone so that you can use it with the API
- Error Message: "Name" parameter not specified.
What's New
- Fixed: Available endpoints didn't update after definition of 'Extended Metadata'.
- Fixed: Incorrect handling of parameters with whitespace in design.
- New: A new parameter Endpoint to explicitly specify the endpoint used for data retrieval.
- New: Usability improvements in the UI for managing parameters.
- New: A design-time log, accessible via a new 'View Log' button (for troubleshooting purposes).
- New: Enhancements in the logging output.
- Fixed: Unable to resize columns in Preview tab.
- Fixed: Failed with error "Unable to cast object of type 'System.Byte[]' to type 'Jurassic.Library.ObjectInstance'" when trying to read certain fields (Thank you, Gaurav).
- New: Component setup dialog now includes data Preview tab.
- New: Introduced component.
Overview
The REST Destination Component is an SSIS Data Flow Component for inserting data into a REST resource.
Parameters
General
Use the General page of the REST Destination dialog to specify the destination REST resource and options.
Select the COZYROC REST connection manager.
Related Topics: REST Connection Manager
Select the destination resource action. This property has the options listed in the following table.
Action Description Create Create new instances in the destination REST resource. Update Update existing instances in the destination REST resource. Delete Delete existing instances from the destination REST resource. Upsert Update and insert records in the destination REST resource. Select the destination REST resource.
Specify the number of rows to be sent as a batch. Default is 200.
Specify additional custom metadata.
Specify additional parameters for data insertion.
Mapping
Use the Mapping page to map input columns to destination REST resource columns.
Select the input columns.
Select the destination REST columns.
Error Output
Use the Error Output page to select error handling options for the component.
View the name of the destination.
Specify what should happen when an error occurs: ignore the failure, redirect the row, or fail the component.
Related Topics: Handling Errors in Data
View a description of the error.
Knowledge Base
- Where can I find the documentation for the REST Destination?
- Error message: Required object attribute(s) are missing from your request: row.id
- How to use an XML configuration in a REST Connection Manager
- How to work with composite records handling in a rest destination component.
- Error Message: No data. Unable to initialize report metadata.
What's New
- New: Usability improvements in the UI for managing parameters.
- Fix: Failed with error "Unsupported value type: System.Guid" when processing
Guid
input (Thank you, Kevin).
- New: Introduced component.
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 authentication, resources, endpoints, data sending and processing for consuming an API in SSIS context.
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 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.
- Authentications - describe any authentication methods that the service supports. You can use "User" parameters - they are specified when configuring the authentication in the REST Connection Manager, "Token" - builtin functionality of REST engine that process and store OAuth token information.
- Resources - describe templates and resources. 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.
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)
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, whileresponse
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
Connects to REST service.
Closes the connection to REST service. Use always after you finish working with the service.
Returns true if connected to the service.
Get REST web service object. The result is RestClient object.
Returns list of available REST resources for specific operation. These are the method parameters:
- operation (uint) - It can be one of the options listed in the following table.
Value Description 0 Return records, which can be read. 1 Return records, which support adding. 2 Return records, which can be updated. 3 Return records, which support remove. 4 Return records, which support upsert.
Get object used to manage REST resource. The method returns IResource object. These are the method parameters:
- name (string) - resource name.
Properties
Specify REST configuration name.
Specify REST service URL.
Specify REST connection parameters. The format is [name]=[value]. Each parameter is separated with newline (\r\n).
Specify token file path.
Specify token file decryption password.
Specify the number of seconds before timing out session connect. The default value of this property is 100 seconds.
Specify if all clients should use the same connection. This property has the options listed in the following table.
Value | Description |
---|---|
True | All clients use same connection (default). |
False | Every client uses different connection. |
Specify log file name.
Specify how many requests per second are permitted.
Specify maximum request retry number. Specify 0 to disable retry.
Specify pause in seconds between retries. The default is 30 seconds.
Specify RegEx pattern to retry if error message matches.
Specify the name or IP address of the proxy server.
Specify the port number on the proxy server to use for the connection.
Specify user name to access the proxy server.
Specify password to access the proxy server.
IField
This interface is used to retrieve REST resource field information.
Properties
Returns field identifier.
Returns field name.
Returns true if field is resource key.
Returns field type.
Returns field length.
Returns field precision.
Returns field scale.
Returns true if field is read-only.
IResource
This interface is used for REST resource management.
Methods
Get resource fields. The method returns array of IField objects.
Constructs new in-memory REST resource instance.
Sets field value in a resource. These are the method parameters:
- instance (object) - resource instance.
- name (string) - field name.
- value (object) - field value.
Returns field value from resource object. These are the method parameters:
- instance (object) - resource instance.
- name (string) - field name.
Retrieves records from REST resource. The method returns array of resource objects. These are the method parameters:
- filter (string array) - array of filters. The filter format is [name]=[value].
- fields (string array) - list of fields to retrieve.
- iterator (ref string) - contains current start iteration position. After data is retrieved, the parameter will contain next iteration position.
- batchSize (int) - number of records to retrieve in one batch.
Creates records in resource. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of objects to create.
- parameters (string array) - array of additional parameters. The parameter format is [name]=[value].
Updates existing records in resource. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of objects with update data.
- parameters (string array) - array of additional parameters. The parameter format is [name]=[value].
Deletes records in resource. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of objects to delete.
- parameters (string array) - array of additional parameters. The parameter format is [name]=[value].
Update or insert records in resource. The method returns array of IStatus objects for each input object. These are the method parameters:
- list (object array) - array of objects to upsert.
- parameters (string array) - array of additional parameters. The parameter format is [name]=[value].
IStatus
This interface is used for reporting back status.
Methods
Returns the REST resource identifier.
Returns the REST error code. If zero, there is no error.
Returns the REST status message.
Returns source JSON object.
ADP Connection
- Fixed: Corrected resource pagination definitions.
- New: Introduced connection.
ArcGIS Feature Connection
- Fixed: Various fixes (Thank you, Carl).
- Fixed: Various fixes.
- New: Introduced connection.
Asana Connection
- Fixed: Various fixes and improvements (Thank you, Stewart).
- Fixed: Incorrect identifier field definition (Thank you, Stan).
- New: Introduced connection.
Constant Contact Connection
- Fixed: Failed with error "Invalid Pagination parameters: If 'next' is provided, no other parameters may be present." (Thank you, Dinesh).
- New: Introduced connection.
Dropbox Connection
- New: Support for short-lived authentication tokens.
- Fixed: Failed to retrieve files from the root folder.
- New: Introduced connection.
Google Analytics Connection
- Fixed: Failed with error "Invalid value at 'report_requests[0].page_token' (TYPE_STRING), 200" when reading Report resource (Thank you, Cameron).
- New: Introduced connection.
Google BigQuery Connection
- New: Support for service account authentication.
- Fixed: Actions that require
item
element failed validation.
- Fixed: Only first 10000 rows read from
Query
resource (Thank you, Sebastian). - Fixed: Data retrieved for
Query
resource was not populated in the correct columns (Thank you, Patrick). - Fixed: Data retrieved for
Tabledata
resource was not populated in the correct columns (Thank you, Angel).
- New: Introduced connection.
Google Drive Connection
- Fixed: Unable to access files on shared drives (Thank you, Steve).
- New: Support for shared drives.
- Fixed: Failed with error "Not Found" when trying to overwrite an existing file.
- Fixed: 'Create remote directory' action in File Transfer Task will now avoid the creation of folders with same name.
- Fixed: Failed with error "Invalid field selection fileId" when reading from Permission resource (Thank you, Karen).
- Fixed: Upload action didn't update the existing file but created a new file.
- New: Introduced connection.
Google Sheets Connection
- Fixed: Various fixes (Thank you Karen).
- New: Introduced connection.
GoToWebinar Connection
- New: Support for RFC 6749 compliant OAuth2 authentication.
- New: Updated to GoToWebinar 2.0 REST API.
- Fixed: Failed to process key fields (Thank you, Brian).
- New: Introduced connection.
HubSpot Connection
- New: Support for custom objects.
- New: Support for 'Analytics', 'Association', 'Ticket', 'Product', 'Line Item', 'CRM Object Property' resources.
- Fixed: Various fixes and enhancements.
- Fixed: Failed to read data from 'Email Campaign' resource (Thank you, Freddie).
- New: Support for OAuth 2.0 authentication.
- New: Support for add/remove of contacts in list.
- New: Introduced connection.
Jira Connection
- Fixed: Authentication signature was invalid because of changes in the REST backend.
- Fixed: Failed to update fields of type 'option-with-child' (Thank you, Keerthi).
- New: Introduced connection.
KRONOS Connection
- Fixed: Defined identifier field type was not large enough.
- Fixed: Failed to retrieve "Saved Reports" resource from US-hosted service (Thank you, Hardik).
- New: Introduced connection.
Magento Connection
- New: Support for 'Company' resource.
- Fixed: 'Catalog Category' resource now shows defined hierarchy.
- Fixed: Failed with error 'Property "SalesorderId" does not have accessor method "getSalesorderId" in class ...' when trying to create record in 'Sales Order Comment' resource (Thank you, Elisabeth).
- New: Introduced connection.
Mailchimp Connection
- Fixed: Only 10 merge fields appeared in 'List Member' resource (Thank you, Jo).
- Fixed: Inserting data into 'List Member' resource failed with error "Index was outside the bounds of the array" when service reported error (Thank you, Steve).
- Fixed: Failed to update data in 'List Member' resource (Thank you, Steve).
- New: Introduced connection.
Marketo Connection
- New: Support for 'Smart Campaign' resource.
- Fixed: REST Source component failed with error (Thank you, Paul):
"Template: {{=external.rest.name=='id'}} Field: undefined Value: undefined Error: TypeError: undefined cannot be converted to an object"
- New: Introduced connection.
OneDrive Connection
- Fixed: Failed to process folder or file containing space in name.
- New: Introduced connection.
Pardot Connection
- New: Support for reading from 'Email', 'Email Stats' resources.
- New: Authentication now placed in request header as required by service.
- New: Support for reading from 'Email Clicks' resource.
- Fixed: The definition for result extraction was incorrect.
- Fixed: Authentication token refresh may fail (Thank you, Porter).
- Fixed: Various fixes (Thank you Porter, Cheryl).
- New: Introduced connection.
Pipedrive Connection
- Fixed: Various fixes (Thank you, Ravi).
- New: Introduced connection.
PureCloud Connection
- Fixed: Failed to retrieve data from 'Billable Usage' resource (Thank you, Anna).
- New: Introduced connection.
QuickBase Connection
- Fixed: Infinite loop when trying to retrieve data from 'System | Table' resource (Thank you, Dean).
- New: Introduced connection.
REST Destination
- New: Usability improvements in the UI for managing parameters.
- Fix: Failed with error "Unsupported value type: System.Guid" when processing
Guid
input (Thank you, Kevin).
- New: Introduced component.
REST Source
- Fixed: Available endpoints didn't update after definition of 'Extended Metadata'.
- Fixed: Incorrect handling of parameters with whitespace in design.
- New: A new parameter Endpoint to explicitly specify the endpoint used for data retrieval.
- New: Usability improvements in the UI for managing parameters.
- New: A design-time log, accessible via a new 'View Log' button (for troubleshooting purposes).
- New: Enhancements in the logging output.
- Fixed: Unable to resize columns in Preview tab.
- Fixed: Failed with error "Unable to cast object of type 'System.Byte[]' to type 'Jurassic.Library.ObjectInstance'" when trying to read certain fields (Thank you, Gaurav).
- New: Component setup dialog now includes data Preview tab.
- New: Introduced component.
SendGrid Connection
- Fixed: Modified to use Export API for 'Contact' resource because the previously used API no longer supports pagination.
- New: Introduced connection.
ServiceNow Connection
- New: Support for reading from database views.
- New: Introduced connection.
Shopify Connection
- New: Support for cursor-based pagination process.
- New: Support for 'Inventory Item', 'Inventory Level', 'Inventory Location' resources.
- Fixed: Various fixes (Thank you Moe, Gaurav).
- New: Introduced connection.
Smartsheet Connection
- Fixed:
clear
parameter was not handled properly in 'Sheet Data' resource Create/Update action (Thank you, Kwame).
- Fixed: Failed with error "Array attributes may not contain null elements: row.cells[]." when updating sheet data (Thank you, Cesar).
- New: Introduced connection.
SurveyMonkey Connection
- Fixed: Various fixes (Thank you, Michael).
- Fixed: Incorrect composite definitions.
- Fixed: Question detail was not retrieved (Thank you, Stu).
- Fixed: Failed to create a record in 'Survey Collector' resource (Thank you, Joelle).
- New: Introduced connection.
Trello Connection
- Fixed: Resource create or update was not working (Thank you, Pablo).
- New: Introduced connection.
Xero Connection
- New: Support for Report API.
- New: Support for OAuth2 authentication.
- Fixed: Authentication signature was invalid because of changes in the REST backend.
- New: Introduced connection.
Zoho CRM Connection
- Fixed: Various fixes (Thank you, Michelle).
- New: Included configuration for v2 API.
- Fixed: Various fixes (Thank you, Michelle).
- New: Introduced connection.
Zuora Connection
- New: Support for ZOQL statements.
- Fixed: Unable to retrieve data from account resource (Thank you, Angel).
- New: Introduced connection.
Knowledge Base
- How to use an XML configuration in a REST Connection Manager
- I see a response returned from the web service, but the preview in REST Source does not show any data.
- How to convert a date into a usable date string that takes into account the timezone so that you can use it with the API
- Where can I find the documentation for the REST Destination?
- Error Message: Unexpected keyword '' at position
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.