Managing Data Destination Insert Errors

Why Redirect Insert Errors

Relational destinations are the workhorse of SSIS. A common requirement is to keep a load running when individual rows violate constraints, by sending the offending rows to an errors table for later inspection. Biml exposes this through the 'ErrorHandling' element on a destination, paired with a second destination that picks up the error output path.

The Scenario

A staging table has a primary key. The first dataflow inserts a small batch of source rows into the empty table. A second dataflow then attempts to insert the same source rows again. Without error handling, the duplicate keys would fail the package. With the redirect configured, the duplicates flow into a separate errors table while the package continues.

The two staging tables are simple:

create table dbo.party
(
  PartyAlternateKey nvarchar(25) not null primary key,
  GivenName nvarchar(50),
  Surname nvarchar(50)
);

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

Starting Biml With a Single Destination

The first version of the package has one dataflow that pulls 100 rows from a source table and lands them into the destination using auto-mapping:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OdbcConnection Name="src_ops" ConnectionString="Driver={SQL Server Native Client 11.0};Server=.;Database=OperationsDW;Trusted_Connection=yes;" />
    <AdoNetConnection Name="dest_stage_ado" ConnectionString="Data Source=.;Initial Catalog=stage;Integrated Security=true;" Provider="System.Data.SqlClient" />
  </Connections>
  <Packages>
    <Package Name="copy_party_with_error" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="dft_party_01">
          <Transformations>
            <OdbcSource Name="src_party" Connection="src_ops">
              <DirectInput>
                select top 100 PartyAlternateKey, GivenName, Surname from dbo.DimParty;
              </DirectInput>
            </OdbcSource>
            <AdoNetDestination ConnectionName="dest_stage_ado" Name="dest_party">
              <ExternalTableOutput Table="[dbo].[party]" />
            </AdoNetDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

Adding a Second Dataflow That Will Hit the Constraint

The second dataflow runs the same insert without the row limit. It is identical to the first except for the source query:

<Dataflow Name="dft_party_02">
  <Transformations>
    <OdbcSource Name="src_party" Connection="src_ops">
      <DirectInput>
        select PartyAlternateKey, GivenName, Surname from dbo.DimParty;
      </DirectInput>
    </OdbcSource>
    <AdoNetDestination ConnectionName="dest_stage_ado" Name="dest_party">
      <ExternalTableOutput Table="[dbo].[party]" />
    </AdoNetDestination>
  </Transformations>
</Dataflow>

Configuring the Error Redirect

The fix is to add 'ErrorHandling' to the primary destination and add a second destination whose 'InputPath' is the error output of the first. The 'OutputPathName' value is the destination name suffixed with '.Error'. Both 'ErrorRowDisposition' and 'TruncationRowDisposition' attributes are required:

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

    <AdoNetDestination ConnectionName="dest_stage_ado" Name="dest_party">
      <ExternalTableOutput Table="[dbo].[party]" />
      <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
    </AdoNetDestination>

    <AdoNetDestination ConnectionName="dest_stage_ado" Name="dest_party_errors">
      <InputPath OutputPathName="dest_party.Error" />
      <ExternalTableOutput Table="[dbo].[party_errors]" />
    </AdoNetDestination>
  </Transformations>
</Dataflow>

Why Bulk Load Sends Every Row to the Error Output

Running the package as it stands sends all 100 rows to the errors table even though only the duplicates should fail. The cause is the destination's default bulk insert behavior. When a destination supports bulk inserts and any row in the buffer fails, the entire buffer is rejected. The same applies to fast loads on ODBC destinations that use batch mode rather than row by row inserts.

To get per-row error redirection, set 'UseFastLoadIfAvailable' to false on the destination that needs to capture individual failures.

Complete Package

The full Biml combines both dataflows. The second destination disables fast load so only the duplicate rows are redirected:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OdbcConnection Name="src_ops" ConnectionString="Driver={SQL Server Native Client 11.0};Server=.;Database=OperationsDW;Trusted_Connection=yes;" />
    <AdoNetConnection Name="dest_stage_ado" ConnectionString="Data Source=.;Initial Catalog=stage;Integrated Security=true;" Provider="System.Data.SqlClient" />
  </Connections>
  <Packages>
    <Package Name="copy_party_with_error" ConstraintMode="Linear">
      <Tasks>
        <Dataflow Name="dft_party_01">
          <Transformations>
            <OdbcSource Name="src_party" Connection="src_ops">
              <DirectInput>
                select top 100 PartyAlternateKey, GivenName, Surname from dbo.DimParty;
              </DirectInput>
            </OdbcSource>
            <AdoNetDestination ConnectionName="dest_stage_ado" Name="dest_party">
              <ExternalTableOutput Table="[dbo].[party]" />
            </AdoNetDestination>
          </Transformations>
        </Dataflow>

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

            <AdoNetDestination ConnectionName="dest_stage_ado" Name="dest_party" UseFastLoadIfAvailable="false">
              <ExternalTableOutput Table="[dbo].[party]" />
              <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
            </AdoNetDestination>

            <AdoNetDestination ConnectionName="dest_stage_ado" Name="dest_party_errors">
              <InputPath OutputPathName="dest_party.Error" />
              <ExternalTableOutput Table="[dbo].[party_errors]" />
            </AdoNetDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>

After truncating the staging tables and rerunning, the second dataflow rejects only the rows that violate the primary key, sending them to the errors table while leaving the existing 100 rows intact in the main table.