REST Configuration OData

Overview

The support of OData APIs (introduced in 2.2 release) is implemented on top of the SSIS+ REST Engine and allows easily querying and modifying data using a OData API. The processing of the OData schema allows the automatic creation of resources and fields when reading and writing data. To learn more about OData see here. OData APIs of versions 2.0 to 4.01 are supported.

Table of Contents

Connection
Reading Data
Writing Data

Connection

The first step of connecting to a OData service and processing the necessary metadata is to create a REST Connection Manager and start authoring custom configuration with OData API template selected.

Then you must specify the OData service endpoint inside Base URL.

That's enough for the extraction and analysis of the OData schema and the generation of the corresponding resources and fields.

---

Reading Data

To request data from a OData service, use a REST Source component and select the connection manager created in the previous step. In the Resource dropdown menu you will see all entities and functions found in the OData service schema to which a request can be send.

Then you must select the type of the resource type in the Endpoint dropdown menu - Entity Set, Entity Single, Singleton or Function as defined in the service contract.

After selecting the resource type, all of its available fields will be listed as a columns in the Columns tab.

It is always advisable to select only fields that will be used in downstream components.

Requesting data for a single element Entity

When requesting a single entity (Endpoint is Entity Single) you need to provide a key value (or values) to identify the entity via the $id parameter in the parameters section.

Or when multiple fields are defined as a key then each key field must have a value in the $id parameter in the form of key value pair separated by comma.

Requesting data via Function call

When requesting a data via Function call (Endpoint is Function) you need to provide a name and value for each function parameter in the parameters section.

Requesting data via Singleton call

When requesting a data via Singleton call (Endpoint is Singleton) there is no need to provide name or value for the defined key in the parameters section.

Using $select parameter to request data only for specific entity fields

This optional parameter can be used to instruct the service to only return values for the fields listed in the $select parameter. All other entity fields will be empty.

Using $filter parameter to request custom data filtering

Another optional parameter which can be used to limit the number of entity elements retrieved from the service. Complex multi-element filtering conditions are possible. Please refer to OData Documentation for query syntax details.

Using $orderby parameter to request custom data ordering

Optional parameter which can be used to request resources in a specific order by providing ordering field name. Optional asc or desc can be included to indicate the order direction. Please refer to OData Documentation for details.

Using $expand parameter to request custom data ordering

$expand is an optional parameter which can be used to request related entities to be included in the response. You can list directly related entity names separated by comma or select them by clicking on the ellipse button in the value field of the $expand parameter. Please refer to OData Documentation for details how to use $expand.

NOTE: If you add or remove Expand fields the columns definitions will be updated to include these new entity fields.

---

Writing Data

To start adding or modifying data, use the REST Destination component and link it to a REST Connection Manager OData connection. In the Resource dropdown menu, you will must select an entity to which mutation requests will be sent.

Following optional parameters can be used to control the mutation behavior:
- IgnoreNullValue - fields with null values will not be included in the request.
- IgnoreUnchanged - fields with same value will not be included in the request.
- AlternateKey - if alternate key column is specified in the service definition then it can be indicated in this parameter.

Finally do the upstream column mappings.

---