Setting Custom Error Handling Behavior

gravatar

Scott Currie

This snippet shows how to override the default error and trunction row behavior for Dataflow components using Biml.

published 08.01.13

last updated 08.01.13


Share

                            


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection Name="AdventureWorks" ConnectionString="Persist Security Info=False;Integrated Security=true;Initial Catalog=AdventureWorks2012;Server=localhost" Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    </Connections>
    <Packages>
        <Package Name="Package" ConstraintMode="Parallel">
            <Tasks>
                <Dataflow Name="Dataflow">
                    <Transformations>
                        <AdoNetSource Name="Source" ConnectionName="AdventureWorks">
                            <DirectInput>
SELECT [ShoppingCartItemID]
      ,[ShoppingCartID]
      ,[Quantity]
      ,[ProductID]
      ,[DateCreated]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[ShoppingCartItem]</DirectInput>
                              <ErrorHandling ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent">
                                <Outputs>
                                    <Output OutputName="Output" ErrorRowDisposition="FailComponent" TruncationRowDisposition="FailComponent">
                                        <Columns>
                                            <Column ColumnName="Quantity" ErrorRowDisposition="IgnoreFailure" TruncationRowDisposition="IgnoreFailure" />
                                            <Column ColumnName="DateCreated" ErrorRowDisposition="IgnoreFailure" TruncationRowDisposition="IgnoreFailure" />
                                        </Columns>
                                    </Output>
                                </Outputs>
                            </ErrorHandling>
                        </AdoNetSource>
                        <AdoNetDestination Name="Destination" ConnectionName="AdventureWorks">
                            <ExternalTableOutput Table="[Sales].[ShoppingCartItem]" />
                        </AdoNetDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

For the sake of simplicity, we are simply copying an AdventureWorks table back to itself, duplicating all rows. You should notice the ErrorHandling section, which allows you to set ErrorRowDisposition and TruncationRowDisposition settings at various levels of granuality. Settings at the output level will apply to all columns in the output. These can be further overridden for each column in the output path.

You are not authorized to comment. A verification email has been sent to your email address. Please verify your account.

Comments

There are no comments yet.