Biml for OData Source and Connection Manager
Why This Pattern Matters
The OData source and OData connection manager are not first class Biml elements. To use them in a Biml package, the source has to be expressed as a 'CustomComponent' and the connection manager as a 'CustomSsisConnection'. The shape of those elements is repetitive, but it is the same shape every time, which means that once one example is in place the rest can be templated.
The Quick Path: Convert From an Existing Package
The fastest way to produce the Biml for an OData source is to build a working SSIS package in the designer first, then run the Convert SSIS Packages to Biml feature in BimlExpress. The generated Biml can be copied into a project and reused.
Two small fixes are usually needed on the converted output. The 'CustomComponent' for the OData source needs 'UsesDispositions="true"' so that the error output is recognized. The data type names on the columns sometimes appear with a 'System' prefix, which has to be removed for the Biml to compile cleanly. After those edits, the Biml is ready to use.
OData Connection Manager in Biml
The OData connection manager is expressed as a 'CustomSsisConnection' with three attributes. 'Name' is the connection name, 'CreationName' identifies the connection type as 'ODATA', and 'ObjectData' carries the inner XML for the connection manager itself:
<CustomSsisConnection
Name="CatalogFeed"
CreationName="ODATA"
ObjectData="
<ODataConnectionManager
UserName=""
Url="https://example.org/odata/Catalog.svc"
ConnectionString="Service Document Url=https://example.org/odata/Catalog.svc;"
MicrosoftOnlineServicesAuth="False"
AuthType="WindowsAuthentication"
/>"
/>
Because the inner content is XML inside an XML attribute, the angle brackets are escaped to '<' and '>', and the inner double quotes are escaped to '"'. After the escaping is reversed, the inner payload is just a standard 'ODataConnectionManager' element of the same shape that an SSIS package would write to its own .dtsx file.
OData Source in Biml
The OData source is a 'CustomComponent'. It declares its component type, references the connection manager, sets a few custom properties, and lists the output columns and the error output:
<CustomComponent Name="Source OData Catalog Items" ComponentTypeName="Microsoft.OData" UsesDispositions="true">
<Connections>
<Connection Name="Connection" ConnectionName="CatalogFeed" />
</Connections>
<CustomProperties>
<CustomProperty Name="CollectionName" DataType="String">Items</CustomProperty>
<CustomProperty Name="DefaultStringLength" DataType="Int32">4000</CustomProperty>
<CustomProperty Name="Query" DataType="String">$select=ItemID,ItemName,ListPrice</CustomProperty>
<CustomProperty Name="ResourcePath" DataType="String"></CustomProperty>
<CustomProperty Name="UseResourcePath" DataType="Boolean">false</CustomProperty>
</CustomProperties>
<OutputPaths>
<OutputPath Name="Output">
<ExternalColumns>
<ExternalColumn Name="ItemID" DataType="Int32" />
<ExternalColumn Name="ItemName" DataType="String" Length="80" />
<ExternalColumn Name="ListPrice" DataType="Decimal" Precision="19" Scale="4" />
</ExternalColumns>
<OutputColumns>
<OutputColumn Name="ItemID" DataType="Int32" ExternalMetadataColumnName="ItemID" />
<OutputColumn Name="ItemName" DataType="String" Length="80" ExternalMetadataColumnName="ItemName" />
<OutputColumn Name="ListPrice" DataType="Decimal" Precision="19" Scale="4" ExternalMetadataColumnName="ListPrice" />
</OutputColumns>
</OutputPath>
<OutputPath Name="Error output" IsErrorOutput="true">
<OutputColumns>
<OutputColumn Name="ItemID" DataType="Int32" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
<OutputColumn Name="ItemName" DataType="String" Length="80" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
<OutputColumn Name="ListPrice" DataType="Decimal" Precision="19" Scale="4" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
</OutputColumns>
</OutputPath>
</OutputPaths>
</CustomComponent>
The 'CustomProperties' section mirrors the Component Properties tab in the SSIS Advanced Editor, and 'OutputPaths' mirrors the Input and Output Properties tab. The 'IsErrorOutput' flag on the second 'OutputPath' is what makes the second path the error output, and the per column 'ErrorRowDisposition' and 'TruncationRowDisposition' attributes mirror the dropdowns on the SSIS Error Output editor.
Putting It Together: Truncate and Load
The connection manager and the source come together inside a normal package. The example below truncates the destination table and then loads from the OData feed into an OLE DB destination:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<OleDbConnection Name="StagingDb" ConnectionString="Data Source=.;Initial Catalog=Staging;Provider=SQLNCLI11;Integrated Security=SSPI;" />
<CustomSsisConnection Name="CatalogFeed" CreationName="ODATA"
ObjectData="<ODataConnectionManager Url="https://example.org/odata/Catalog.svc" ConnectionString="Service Document Url=https://example.org/odata/Catalog.svc;" AuthType="WindowsAuthentication" MicrosoftOnlineServicesAuth="False" UserName="" />" />
</Connections>
<Packages>
<Package Name="Load_Catalog_Items" ConstraintMode="Linear">
<Tasks>
<ExecuteSQL Name="Truncate Catalog Items" ConnectionName="StagingDb">
<DirectInput>TRUNCATE TABLE [stg].[CatalogItems]</DirectInput>
</ExecuteSQL>
<Dataflow Name="Load Catalog Items">
<Transformations>
<CustomComponent Name="Source OData Catalog Items" ComponentTypeName="Microsoft.OData" UsesDispositions="true">
<Connections>
<Connection Name="Connection" ConnectionName="CatalogFeed" />
</Connections>
<CustomProperties>
<CustomProperty Name="CollectionName" DataType="String">Items</CustomProperty>
<CustomProperty Name="DefaultStringLength" DataType="Int32">4000</CustomProperty>
<CustomProperty Name="Query" DataType="String">$select=ItemID,ItemName,ListPrice</CustomProperty>
<CustomProperty Name="ResourcePath" DataType="String"></CustomProperty>
<CustomProperty Name="UseResourcePath" DataType="Boolean">false</CustomProperty>
</CustomProperties>
<OutputPaths>
<OutputPath Name="Output">
<OutputColumns>
<OutputColumn Name="ItemID" DataType="Int32" ExternalMetadataColumnName="ItemID" />
<OutputColumn Name="ItemName" DataType="String" Length="80" ExternalMetadataColumnName="ItemName" />
<OutputColumn Name="ListPrice" DataType="Decimal" Precision="19" Scale="4" ExternalMetadataColumnName="ListPrice" />
</OutputColumns>
<ExternalColumns>
<ExternalColumn Name="ItemID" DataType="Int32" />
<ExternalColumn Name="ItemName" DataType="String" Length="80" />
<ExternalColumn Name="ListPrice" DataType="Decimal" Precision="19" Scale="4" />
</ExternalColumns>
</OutputPath>
<OutputPath Name="Error output" IsErrorOutput="true">
<OutputColumns>
<OutputColumn Name="ItemID" DataType="Int32" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
<OutputColumn Name="ItemName" DataType="String" Length="80" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
<OutputColumn Name="ListPrice" DataType="Decimal" Precision="19" Scale="4" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent" />
</OutputColumns>
</OutputPath>
</OutputPaths>
</CustomComponent>
<OleDbDestination Name="Destination Staging CatalogItems" ConnectionName="StagingDb">
<InputPath OutputPathName="Source OData Catalog Items.Output" />
<ExternalTableOutput Table="[stg].[CatalogItems]" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
Summary
OData support in SSIS is reachable from Biml through the generic 'CustomSsisConnection' and 'CustomComponent' elements. Converting an existing OData package with BimlExpress is the fastest way to produce a working starting point, and the converted Biml only needs minor cleanup before it can be templated across many packages.