Skip to main content

Data Source Package Development

info

Available for Flex (SSIS) only

Overview​

There are two types of data source packages in COZYROC CLOUD. OData and TDS data source packages.

  • OData (Open Data Protocol) is a standard that defines rules for building and consuming RESTful APIs.
  • TDS (Tabular Data Stream) is an application‑layer protocol that defines how database clients and servers exchange requests and results over a network connection. It is primarily used by Microsoft SQL Server and related products to send SQL queries and return tabular result sets. TDS standardizes the structure and sequencing of messages for database conversations so that client libraries and tools can focus on query and business logic instead of low‑level wire formatting.

SSIS packages built with DataReader Destination can be used to access data from any OData or TDS aware application (e.g. Power BI).

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.

img

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

img

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

img

  1. 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.

img

  1. Save and upload your package to COZYROC Cloud.

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

img

Package Parameters​

Package parameters are accessible through the OData and TDS services 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 or TDS service call.

img

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

Package Deployment​

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

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​

note

Applicable only to OData packages

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..

img

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.

img

  1. 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.

img

  1. Disable the script task.

img

  1. Save and upload your package.

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

img

  1. 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 execute url​

img

  • TDS query and connection string​

img

In case of source changes you can refresh the resources by clicking the button next to the resource selection.

img

Refreshable resource package parameterization​

Use SSIS+ REST Source component together with the SSIS+ Data Flow Task Plus. This configuration enables integration with dynamic REST resources.

The REST Source component exposes a dedicated parameter named ResourceParameter. This parameter must be bound to a package parameter of type String. It is recommended to name this package parameter ResourceParameters, although any other name can be used.

img

The ResourceParameter is a special parameter that allows you to pass user parameters to the REST API. User parameters are the input parameters defined by the REST endpoint you are consuming. Each REST resource can define its own set of user parameters.

img

We've developed resource request builders for OData and TDS which you can use to build your requests and assign user parameters conveniently.