BimlFlex OData Source

BimlFlex can use custom source components, such as OData to load data from bespoke sources.

Getting Started

OData is loaded through BimlFlex by adding a custom source connection and a custom source definition that matches the OData source format.

This source is available for Projects using the SSIS: Source -> Target Integration Template.

The example below uses the publicly available Northwind OData v3 data source from https://www.odata.org/odata-services/.

Metadata

To control the load of OData source data, use the following metadata settings.

Start with Sample Metadata Set 01 - MSSQL Starting Point.

Connection

Add a new connection to the OData Source using the Connection Type Script Source and System Type Microsoft SQL Server.

Sample Connection:

Attribute	Value
Name	NWND_SRC
Integration Stage	Source System
Connection Type	Script Source
System Type	Microsoft SQL Server
Description	Sample OData source connection.
Connection String	`{Any Value}` Note: Not used, see Connection Override Extension Point
Catalog	NWND_SRC
Record Source	nwnd
Persist History	true
Threads	0

Note

The actual definition of this connection will be defined using the Connection Override Extension Point below.

Batch

Add a separate Batch for the OData load.

Attribute	Value
Name	EXT_NWND_SRC
Precedence Constraint	Success
Threads	2
Containers	0
Description	Sample Source Batch configuration for Northwind OData
Use Ssis Express	false
Use Orchestration	true

Project

Add a separate Project for the OData load. Set the OData Connection as the source for the Project.

Attribute	Value
Name	EXT_NWND_SRC
Parent Project	`{empty}`
Batch	EXT_NWND_SRC
Description	Sample Source Project configuration for Northwind OData
Integration Template	SQL Server Integration Service (SSIS)
Pushdown Processing	true
Source Connection	NWND_SRC
Stage Connection	BFX_STG
Persistent STage Connection	BFX_ODS
Target Connection	BFX_RDV

Object

Add the Object information through the BimlFlex App.

Attribute	Value
Project	EXT_NWND_SRC
Connection	NWND_SRC
Schema	odata
Object Name	Products
Object Type	Table

Note

Metadata Import is not supported yet for OData so Object and Column information will have to be manually added.

Columns

Add the Column information as described in the OData service definition, with correct data types.

Conneciton	Object	Column	Data Type	Change Type	Ordinal	Primary Key	Integration Key	Source Key
NWND_SRC	odata.Products	ProductID	Int32	Key	1	true	true	true
NWND_SRC	odata.Products	ProductName	String(40)	Update	2	false	false	false
NWND_SRC	odata.Products	SupplierID	Int32	Update	3	false	false	false
NWND_SRC	odata.Products	CategoryID	Int32	Update	4	false	false	false
NWND_SRC	odata.Products	QuantityPerUnit	String(20)	Update	5	false	false	false
NWND_SRC	odata.Products	UnitPrice	Decimal(19,4)	Update	6	false	false	false
NWND_SRC	odata.Products	UnitsInStock	Int16	Update	7	false	false	false
NWND_SRC	odata.Products	UnitsOnOrder	Int16	Update	8	false	false	false
NWND_SRC	odata.Products	ReorderLevel	Int16	Update	9	false	false	false
NWND_SRC	odata.Products	Discontinued	Bool	Update	10	false	false	false

Note

Metadata Import is not supported yet for OData so Object and Column information will have to be manually added.

Connection Override Extension Point

Connection specification. Uses an Extension Point to connect to the OData Source.

This Connection Override Extension Point adds the connection Object for the OData source.

Source, authentication etc. are all embedded in the ObjectData, for configurable options, add project parameters and inject them here. This connection connects to odata.org to access a publicly available data set using OData v3 and the Northwind sample source.

Sample Extension Point: NWND_SRC_ConnectionOverride.biml

Note

If converting to your own solution, the target= should be set to the CONNECTION NAME of the OData Connection.

Additionally, ensure you configure the attributes of the <CustomSsisConnection/> to reflect the values you want.

When defining the ObjectData, ensure you are using the proper XML Escape Characters, and setting the Url, ConnectString, Service Document, and other fields required for custom component configuration, as needed.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="ConnectionOverride" target="NWND_SRC" #>
<#@ property name="connection" type="BimlFlexModelWrapper.ConnectionsWrapper" #>

<!--
This ConnectionOverride Extension Point adds the connection Object for the OData source.
Source, authentication etc are all embedded in the ObjectData, for configurable options, add project parameters and inject them here.
This connection connects to odata.org to access a publicly available data set using OData V3 and the Northwind sample source.
-->

<CustomSsisConnection
    Name="NWND_SRC"
    CreationName="ODATA"
    ObjectData="&lt;ODataConnectionManager UserName=&quot;&quot; Url=&quot;http://services.odata.org/V3/Northwind/Northwind.svc&quot; ConnectionString=&quot;Service Document Url=https://services.odata.org/V3/Northwind/Northwind.svc;&quot; MicrosoftOnlineServicesAuth=&quot;False&quot; AuthType=&quot;WindowsAuthentication&quot; /&gt;"
/>

Important

In order to get the Northwind OData connection to work it requires http instead of https. The fix provided in TLS Issue with SSIS package while accessing OData Source like Dynamics AX Online may also be required.

Source Override Extension Point

The following Extension Point is also required in order to properly build out the data flow.

Sample Extension Point: EXT_NWND_SRC_SourceOverride.biml

Note

If converting to your own solution, the target= should be set to the PROJECT NAME of the OData Project.

<#@ extension bundle="BimlFlex.bimlb" extensionpoint="SourceOverride" target="EXT_NWND_SRC"#>
<#@ property name="table" type="BimlFlexModelWrapper.ObjectsWrapper" #>

<!--
This SourceOverride Extension Point dynamically creates the source component for the OData Objects based on the metadata.
The Object name is used as the CollectionName for the source and the columns are dynamically added to the definition.
Target is set to the Batch name and inheritance is switched on to apply to all objects in the Batch
-->

<#
CustomOutput.ObjectInherit = true;
TableObject sourceTable = new TableObject(table, table.Connection.RelatedItem.IntegrationStage, "SRC");
#>

<CustomComponent Name="OData Source" ComponentTypeName="Microsoft.OData" ContactInfo="OData Source Component;Microsoft Corporation; Microsoft SQL Server; © Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0" UsesDispositions="true">
    <CustomProperties>
        <CustomProperty Name="CollectionName" DataType="String" SupportsExpression="true" Description="Name of the collection to be retrieved from the service."><#=sourceTable.ObjectName #></CustomProperty>
        <CustomProperty Name="DefaultStringLength" DataType="Int32" Description="The default maximum length for a string that has no maxlength specified in the schema.">255</CustomProperty>
        <CustomProperty Name="Query" DataType="String" SupportsExpression="true" Description="Query in the url."></CustomProperty>
        <CustomProperty Name="ResourcePath" DataType="String" SupportsExpression="true" Description="Resource path of the collection to be retrieved from the service."></CustomProperty>
        <CustomProperty Name="UseResourcePath" DataType="Boolean" SupportsExpression="true" Description="When set to true, use the resource path as requested collection; otherwise, use the collection name.">false</CustomProperty>
    </CustomProperties>
    <OutputPaths>
        <OutputPath Name="Output">
            <OutputColumns>
                <# foreach (var col in sourceTable.Columns.Where(c => c.IsDerived=="N")) `{ #>
                <OutputColumn Name="<#=col.ColumnName #>" <#=col.GetDataTypeBiml() #> ExternalMetadataColumnName="<#=col.ColumnName #>" ErrorOrTruncationOperation="Conversion" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
                <# }` #>
            </OutputColumns>
            <ExternalColumns>
                <# foreach (var col in sourceTable.Columns.Where(c => c.IsDerived=="N")) `{ #>
                <ExternalColumn Name="<#=col.ColumnName #>"  <#=col.GetDataTypeBiml() #> />
                <# }` #>
            </ExternalColumns>
        </OutputPath>
        <OutputPath Name="Error output" IsErrorOutput="true">
            <OutputColumns>
                <# foreach (var col in sourceTable.Columns.Where(c => c.IsDerived=="N")) `{ #>
                <OutputColumn Name="<#=col.ColumnName #>" <#=col.GetDataTypeBiml() #> />
                <# }` #>
            </OutputColumns>
        </OutputPath>
    </OutputPaths>
    <Connections>
        <Connection Name="Connection" ConnectionName="<#=sourceTable.ConnectionName #>" />
    </Connections>
    <# CustomOutput.OutputPathName = @"OData Source.Output"; #>
</CustomComponent>

More Information

Certain machines might get errors like the following running this sample:

OData Source Error: Cannot acquire a managed connection from the run-time connection manager. Error: OData Source failed validation and returned error code 0xC020801F.

While there are several prerequisites and configurations needed based on the SSIS and Visual Studio version, such as if the OData components are pre-installed or not. These error messages might also stem from a mismatch in security configurations for the protocol used.

Refer to the recommendations in this Microsoft documentation page for more information:

TLS Issue with SSIS package while accessing OData Source like Dynamics AX Online