The ODBC Destination in Biml

Why ODBC Destinations Matter

Most production SSIS extracts land into staging tables in a relational engine. Biml exposes the ODBC destination through 'OdbcDestination', which supports auto mapping by column name, explicit source-to-target mapping, and a per-column 'IsUsed' flag for opting individual columns out of the auto map.

The Source

A simple query against a source dimension provides the input columns:

select PartyAlternateKey, GivenName, Surname from dbo.DimParty;

Two staging tables hold the result. The first uses identical column names so auto mapping works without configuration. The second uses different column names so an explicit mapping is required:

create table dbo.party_automapping
(
  PartyAlternateKey nvarchar(25),
  GivenName nvarchar(50),
  Surname nvarchar(50)
);

create table dbo.party_manual
(
  party_cd nvarchar(25),
  first_name nvarchar(50),
  last_name nvarchar(50)
);

Package Skeleton

The package contains two parallel dataflows. The skeleton declares both connections and leaves the transformations empty for now:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OdbcConnection Name="dest_stage" ConnectionString="Driver={SQL Server Native Client 11.0};..." />
    <OdbcConnection Name="src_ops" ConnectionString="Driver={SQL Server Native Client 11.0};..." />
  </Connections>
  <Packages>
    <Package Name="copy_party" ConstraintMode="Parallel">
      <Tasks>
        <Dataflow Name="party_manualmapping">
          <Transformations>
          </Transformations>
        </Dataflow>
        <Dataflow Name="party_automapping">
          <Transformations>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The ODBC Source

Each dataflow uses the same source query, expressed as 'DirectInput':

<OdbcSource Name="src_party" Connection="src_ops">
  <DirectInput>
    select PartyAlternateKey, GivenName, Surname from dbo.DimParty;
  </DirectInput>
</OdbcSource>

Auto Mapping by Column Name

The destination requires a name, a connection, and a target table. With matching column names on the source and destination, Biml maps columns automatically:

<Dataflow Name="party_automapping">
  <Transformations>
    <OdbcSource Name="src_party" Connection="src_ops">
      <DirectInput>
        select PartyAlternateKey, GivenName, Surname from dbo.DimParty;
      </DirectInput>
    </OdbcSource>
    <OdbcDestination Name="dest_party" Connection="dest_stage">
      <ExternalTableOutput Table="[dbo].[party_automapping]" />
    </OdbcDestination>
  </Transformations>
</Dataflow>

The 'ExternalTableOutput' element identifies the target table. Schema qualification is optional. Without it, the connection's default schema applies.

Explicit Column Mapping

To map source columns to differently named target columns, add a 'Columns' child node to the destination. Only the columns listed are remapped. Columns not listed still auto map by name when both sides agree:

<OdbcDestination Name="dest_party" Connection="dest_stage">
  <ExternalTableOutput Table="[dbo].[party_manual]" />
  <Columns>
    <Column SourceColumn="PartyAlternateKey" TargetColumn="party_cd" />
    <Column SourceColumn="GivenName" TargetColumn="first_name" />
    <Column SourceColumn="Surname" TargetColumn="last_name" />
  </Columns>
</OdbcDestination>

The 'SourceColumn' and 'TargetColumn' attributes drive the mapping.

Complete Package

Combining both dataflows produces the full package:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OdbcConnection Name="dest_stage" ConnectionString="Driver={SQL Server Native Client 11.0};..." />
    <OdbcConnection Name="src_ops" ConnectionString="Driver={SQL Server Native Client 11.0};..." />
  </Connections>
  <Packages>
    <Package Name="copy_party" ConstraintMode="Parallel">
      <Tasks>
        <Dataflow Name="party_manualmapping">
          <Transformations>
            <OdbcSource Name="src_party" Connection="src_ops">
              <DirectInput>
                select PartyAlternateKey, GivenName, Surname from dbo.DimParty;
              </DirectInput>
            </OdbcSource>
            <OdbcDestination Name="dest_party" Connection="dest_stage">
              <ExternalTableOutput Table="[dbo].[party_manual]" />
              <Columns>
                <Column SourceColumn="PartyAlternateKey" TargetColumn="party_cd" />
                <Column SourceColumn="GivenName" TargetColumn="first_name" />
                <Column SourceColumn="Surname" TargetColumn="last_name" />
              </Columns>
            </OdbcDestination>
          </Transformations>
        </Dataflow>
        <Dataflow Name="party_automapping">
          <Transformations>
            <OdbcSource Name="src_party" Connection="src_ops">
              <DirectInput>
                select PartyAlternateKey, GivenName, Surname from dbo.DimParty;
              </DirectInput>
            </OdbcSource>
            <OdbcDestination Name="dest_party" Connection="dest_stage">
              <ExternalTableOutput Table="[dbo].[party_automapping]" />
            </OdbcDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Opting Out Individual Columns

To rely on auto mapping but skip a single column, list that column in 'Columns' with 'IsUsed' set to false. Other columns continue to auto map:

<OdbcDestination Name="dest_party" Connection="dest_stage">
  <ExternalTableOutput Table="[dbo].[party_automapping]" />
  <Columns>
    <Column SourceColumn="PartyAlternateKey" IsUsed="false" />
  </Columns>
</OdbcDestination>

This pattern keeps the rest of the destination configuration short while excluding selected columns from the load.