OData Package Development

info

Available for Flex (SSIS) only

Overview

OData (Open Data Protocol) is a standard that defines rules for building and consuming RESTful APIs. SSIS packages built with DataReader Destination can be used to access data from any OData aware application (e.g. PowerBI).

Requirements and Recommendations

Please, see 'Developing SSIS packages' section of the documentation.

Package Development

Create your package as you will do for any normal SSIS package. As a final step in the Data Flow add DataReader Destination Component and select the input columns.

1. Add DataReader Destination Component.

2. Connect the data source output to the DataReader Destination Component.

3. Right click on the destination component and select 'Edit' from the context menu.

4. In the configuration window select the input columns from the data source to be included in the DataReader Destination output. These selected columns will be transferred to the OData result set.

note

At least one column must be selected.

5. Save and upload your package to COZYROC Cloud.

6. The output columns metadata will automatically extracted and made available in the Resources section of your package details page.

Columns Metadata

Static resources

When you use the standard Microsoft Data Flow Task in your package the output columns metadata will be statically defined in your package. That means the output columns will remain the same unless you upload a new version of your package. Depending on the datasource this may or may not be an issue.

Dynamic resources

If you want to automatically accomodate changes in your source columns, you may want to utilize COZYROC Data Flow Task Plus instead. This way you will be able to keep your columns metadata up to date.

By simply exchanging Microsoft Data Flow Task with COZYROC Data Flow Task Plus the columns metadata will be refreshed on every execution. With this approach you will always receive your data correctly, but you won't be able check the metadata prior to execution. The Resources panel on the details page will show the following message The fields for this resource are determined during execution..

Refreshable resources

To get the best of both worlds you can make use of our JavaScript Task in combination with COZYROC Data Flow Task Plus to allow refreshing of the metadata with a user action. This approach is currently supported only for packages utilizing the COZYROC REST Connection Manager and REST Source by using the Get REST Resources Metadata Task template for your script task. For other data sources you can either implement your own script based on the template or contact support for assistance.

1. Develop your package as usual using COZYROC Data Flow Task Plus.

2. Add a package parameter named e.g. Resource that controls the REST resource your package is executed for.

3. Add a JavaScript Task beside your data flow task and choose Get REST Resources Metadata Task from the list of pre-built scripts.

3. Configure the parameters of the script task by selecting your REST Connection Manager, a variable for the extracted columns metadata and the resource package parameter created in step 2.

4. Disable the script task.

5. Save and upload your package.

6. On you package details page you'll be asked to refresh resources first before you are able to execute your package.

7. After the operation completes all resources from your REST data source will be available in the Resources section. Each resource will have its own OData URL. In case of source changes you can refresh the resources by clicking the button next to the resource selection.

Package Parameters

Package parameters are accessible through the OData service and can be used to implement additional logic (filters, conditions etc.) in a data flow. Parameter values can be set at the time of the OData service call.

Example: Package with a parameter used in a Conditional Split transformation.

Package Deployment

Please, refer to 'Uploading SSIS packages' section of the documentation.