Skip to main content

Biml Configuration for the Lookup Transform

Why the Lookup Earns Attention​

The Lookup transformation is the most common non source, non destination component in an SSIS data flow. It pulls additional columns onto the buffer based on a match against another data set. This walkthrough covers the three configurations encountered most often: a bare bones lookup that relies on column name auto matching, a lookup with explicit column mapping for differently named columns, and a lookup that routes unmatched rows to a separate output.

Stage Schema​

The destination table holds the columns the package will write to. No constraints sit on the table so the examples can route both matched and unmatched rows into it:

create table stg.Customer
(
  CustomerCode nvarchar(25) NOT NULL,
  GivenName nvarchar(50),
  FamilyName nvarchar(50)
);

The Skeleton​

The starting Biml below defines the connections, package, data flow, source, and destination, with a placeholder where the Lookup will go:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="SalesSource" ConnectionString="..." />
    <OleDbConnection Name="StagingDb" ConnectionString="..." />
  </Connections>
  <Packages>
    <Package Name="LookupCustomerNames" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="EnrichCustomerCodes">
          <Transformations>

            <OleDbSource Name="ReadCodes" ConnectionName="SalesSource">
              <DirectInput>select CustomerCode from sales.Customer;</DirectInput>
            </OleDbSource>

            <!-- Lookup goes here -->

            <OleDbDestination Name="WriteCustomers" ConnectionName="StagingDb">
              <ExternalTableOutput Table="stg.Customer" />
            </OleDbDestination>

          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Part 1: Bare Bones Lookup​

The minimal Lookup needs four pieces: the data source for the lookup table, a query that defines the lookup data set, the input column to match on, and the columns to append to the buffer. When the match column has the same name on both sides, only the source column needs naming:

<Lookup Name="MatchCustomerNames" OleDbConnectionName="SalesSource">
  <DirectInput>
    select CustomerCode, GivenName, FamilyName from sales.Customer;
  </DirectInput>
  <Inputs>
    <Column SourceColumn="CustomerCode" />
  </Inputs>
  <Outputs>
    <Column SourceColumn="GivenName" />
    <Column SourceColumn="FamilyName" />
  </Outputs>
</Lookup>

Recall that a Lookup is not a JOIN. The buffer flows in, columns get appended where matches exist, and the default behavior on a miss is to fail the component.

Part 2: Explicit Column Mapping​

Production sources rarely use the same column name on both sides. When they differ, the 'Inputs' block must list both 'SourceColumn' (the buffer column name) and 'TargetColumn' (the lookup query column name). The example below renames the buffer column to 'code_lhs' and the lookup column to 'code_rhs', then maps them explicitly. Because the buffer column no longer matches the destination column name either, the destination needs an explicit column mapping as well:

<OleDbSource Name="ReadCodes" ConnectionName="SalesSource">
  <DirectInput>select CustomerCode as code_lhs from sales.Customer;</DirectInput>
</OleDbSource>

<Lookup Name="MatchCustomerNames" OleDbConnectionName="SalesSource">
  <DirectInput>
    select
      CustomerCode as code_rhs,
      GivenName,
      FamilyName
    from sales.Customer;
  </DirectInput>
  <Inputs>
    <Column SourceColumn="code_lhs" TargetColumn="code_rhs" />
  </Inputs>
  <Outputs>
    <Column SourceColumn="GivenName" />
    <Column SourceColumn="FamilyName" />
  </Outputs>
</Lookup>

<OleDbDestination Name="WriteCustomers" ConnectionName="StagingDb">
  <ExternalTableOutput Table="stg.Customer" />
  <Columns>
    <Column SourceColumn="code_lhs" TargetColumn="CustomerCode" />
  </Columns>
</OleDbDestination>

A composite key follows the same shape: one 'Column' element per key column inside the 'Inputs' block.

Part 3: Configuring No Match Behavior​

The default no match behavior fails the task. Other valid values for 'NoMatchBehavior' on the 'Lookup' element are:

  • 'IgnoreFailure' passes nulls into the lookup output columns for unmatched rows.
  • 'RedirectRowsToErrorOutput' sends unmatched rows down the error output.
  • 'RedirectRowsToNoMatchOutput' sends unmatched rows down a dedicated no match output.
  • 'FailComponent' is the default, kept here for completeness.

When unmatched rows are redirected, downstream destinations should consume the resulting output paths explicitly through 'InputPath'. Skipping that step does not raise a build error, but rows landing in the unconsumed output are dropped. The data flow below limits the lookup table to one hundred rows so that the unmatched path receives traffic, and routes both matched and unmatched rows to the staging table for inspection:

<Dataflow Name="EnrichCustomerCodesWithRouting">
  <Transformations>
    <OleDbSource Name="ReadCodes" ConnectionName="SalesSource">
      <DirectInput>select CustomerCode from sales.Customer;</DirectInput>
    </OleDbSource>

    <Lookup Name="MatchCustomerNames" OleDbConnectionName="SalesSource" NoMatchBehavior="RedirectRowsToNoMatchOutput">
      <DirectInput>
        select top 100
          CustomerCode,
          GivenName,
          FamilyName
        from sales.Customer;
      </DirectInput>
      <Inputs>
        <Column SourceColumn="CustomerCode" />
      </Inputs>
      <Outputs>
        <Column SourceColumn="GivenName" />
        <Column SourceColumn="FamilyName" />
      </Outputs>
    </Lookup>

    <OleDbDestination Name="WriteMatched" ConnectionName="StagingDb">
      <InputPath OutputPathName="MatchCustomerNames.Match" />
      <ExternalTableOutput Table="stg.Customer" />
    </OleDbDestination>

    <OleDbDestination Name="WriteUnmatched" ConnectionName="StagingDb">
      <InputPath OutputPathName="MatchCustomerNames.NoMatch" />
      <ExternalTableOutput Table="stg.Customer" />
    </OleDbDestination>

  </Transformations>
</Dataflow>

Sending the no match output to the same destination as the match output is for illustration only. Production code typically directs unmatched rows to an audit table for review.