Using the Premium OData Source Component

The Premium OData Source component is an SSIS data flow source component that can be used to read from OData endpoints. There are two pages that can be configured and worked with in the Premium OData Source component.

  • General
  • Columns

General

The General page can be used to configure the general configuration settings for the Premium OData Source component.

SSIS OData Source Component - General

Connection Manager

A Connection Manager is required for the Premium OData Source component to connect to the endpoint. A list of available connection managers will populate the drop-down.

Binding Entity

The binding entities available in the OData service can be chosen in this field. Depending on the type of these entities, other properties would be populated in the Premium OData Source component. The following entities are supported:

  • Unbound function
  • Collection of entities
  • Entity
Endpoint

The endpoint can be one of the below, depending on how you wish to read the data.

  • Query
  • Search
  • Get by ID
Query (Available only for endpoint as Query)

The Query that can be used to build the $filter query option. Click on the ellipsis "…" button on the side to open the OData Query Builder, where you can build the query.

SSIS OData Source component - Query Builder

The Query Editor builder supports adding filter conditions with complex type and enum properties.

  • Add condition (+): Adds a condition to be used in query.
  • Remove condition (-): Removes the condition that was added.
  • Move Right: Moves the condition right and adds a bracket to the left.
  • Move Left: Moves the condition left and adds a bracket to the right.
  • Remove All: Removes all conditions
Order By button

The order by clause can be specified here. Click on the ellipsis (…) to open the OData Order By Builder.

SSIS OData Source component - Order By Builder

  • Add line (+): Adds a new line to specify the order by field.
  • Remove line (-): Removes the specific order by line that was specified.
  • The drop-down near the lines can be used to specify whether the ordering should be Ascending or Descending
  • Remove All: Removes all order by lines.
Batch Size

The Batch Size option allows you to specify how many records you want to retrieve per service call. The default value is 1000.

Note: The Batch Size option is only available when using Generic Service Implementation in OData Connection Manager.

Expand

Set $expand query option, which can be used to include related resources. Clicking on the ellipsis button opens the OData Expand Editor. Here you can check the properties that are available. The uncheck all button would remove all selections.

SSIS OData Source Component - Expand Editor

Parameters (Only available for Endpoint chosen as Search or Get By Id)

Specify the parameter that can be used to search in the service.

Additional Binding

This option allows you to specify any additional bindings to other entities that you wish to retrieve from your OData service.

Link ID Mode

The Link ID Mode option allows you to specify how _LinkIId field should be populated.

  • Implicit (RowIndex): a special field that contains the current count of this output node.
  • Explicit (Id): the _LinkId column will be populated with the Id of the parent record.
Target Entity

This field will display the Target Entity details based on the selected properties.

Binding Path

The Binding Path based on the previous properties that were chosen would be displayed in this field.

Target URL

The Target URL for the endpoint with the required parameters and query would be displayed in this field.

Preview

The Preview button, when clicked, will show the request the component will send based on the selected configuration. The Send Request button in the OData preview window would allow you to send an HTTP request and get the response.

SSIS OData Source component - Preview

Refresh Component Button

Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each field to its most recent metadata.

Expression fx Button

Click the fx button to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Button

Click the Generate Documentation button to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Invoke an Unbound Function

You can invoke an unbound function using the OData Source Component and provide values to the Input Parameters defined for it.

SSIS OData Source Component - Invoke an Unbound Function

Invoke a Bound Function

You can invoke a bound function using the OData Source Component, and specify additional bindings for it while providing values to the required Input Parameters.

SSIS OData Source Component - Invoke a Bound Function

Navigate to Related Entities

Using the OData Source component, you can easily navigate to related entities by selecting the additional entities you want to read from:

SSIS OData Source Component - Invoke a Bound Function

Columns

The Columns page gives the metadata related details for the Premium OData Source component, based on the endpoint it is connected to, and other properties that were specified in the General page.

SSIS OData Source Component - Columns

On the top left of the grid, the checkbox is used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

The Columns page grid consists of:

  • OData Field: Column that will be retrieved from the endpoint.
  • Data Type: The data type of this field.