Combine Error Outputs with a Union All

Reads from 'dbo.Src' (all string columns) and pushes the rows through a 'DataConversion' that casts 'TableColumn2' and 'TableColumn3' to 'Int32' before writing to 'dbo.Dst'. Both the 'DataConversion' and the 'OleDbDestination' have 'ErrorRowDisposition="RedirectRow"' so any row that fails conversion or fails to insert is redirected. A 'UnionAll' merges both error streams, marks the converted Int32 columns as 'IsUsed="false"' on the destination input so the union output keeps the original string shape, and writes the combined failed rows to 'C:\test\FailOutput.txt' via a 'FlatFileDestination'.

The 'Annotations' block contains the 'CREATE TABLE' DDL and seed inserts for 'dbo.Src' and 'dbo.Dst' in the 'Sandbox' database, plus a reminder to create an empty 'FailOutput.txt' file at 'C:\test' before running the package. Adjust the 'SandboxConn' connection string to point at your own SQL Server instance, and change the output path on 'FailOutputConn' if you need the failure log written elsewhere.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Annotations>
    <Annotation>
      Tables and Data Script:

      CREATE TABLE [dbo].[Src](
      [TableColumn1] [nvarchar](255) NOT NULL,
      [TableColumn2] [nvarchar](255) NOT NULL,
      [TableColumn3] [nvarchar](255) NOT NULL,
      [TableColumn4] [nvarchar](255) NOT NULL
      )

      CREATE TABLE [dbo].[Dst](
      [TableColumn1] [nvarchar](255) NOT NULL,
      [TableColumn2] [int] NOT NULL,
      [TableColumn3] [int] NOT NULL,
      [TableColumn4] [nvarchar](255) NOT NULL
      )
      GO

      INSERT [dbo].[Src] ([TableColumn1], [TableColumn2], [TableColumn3], [TableColumn4]) VALUES (N'One', N'1', N'1', N'One')
      INSERT [dbo].[Src] ([TableColumn1], [TableColumn2], [TableColumn3], [TableColumn4]) VALUES (N'Two', N'2', N'2', N'Two')
      INSERT [dbo].[Src] ([TableColumn1], [TableColumn2], [TableColumn3], [TableColumn4]) VALUES (N'Three', N'Three', N'3', N'Three')
      INSERT [dbo].[Src] ([TableColumn1], [TableColumn2], [TableColumn3], [TableColumn4]) VALUES (N'Four', N'4', N'Four', N'Four')

    </Annotation>
    <Annotation>
      Flat file:
      Add an empty text file as follow:
        Name: FailOutput.txt
        Location: C:\test
    </Annotation>
  </Annotations>
  <Connections>
    <FlatFileConnection Name="FailOutputConn" FileFormat="FailOutput" FilePath="C:\test\FailOutput.txt" />
    <Connection Name="SandboxConn" ConnectionString="Data Source=.;Initial Catalog=Sandbox;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" />
  </Connections>
  <Databases>
    <Database Name="Sandbox" ConnectionName="SandboxConn" />
  </Databases>
  <FileFormats>
    <FlatFileFormat Name="FailOutput">
      <Columns>
        <Column Name="TableColumn1" DataType="String" Length="255" Delimiter="Comma" />
        <Column Name="TableColumn2" DataType="String" Length="255" Delimiter="Comma" />
        <Column Name="TableColumn3" DataType="String" Length="255" Delimiter="Comma" />
        <Column Name="TableColumn4" DataType="String" Length="255" Delimiter="Comma" />
        <Column Name="ErrorCode" Delimiter="Comma" />
        <Column Name="ErrorColumn" Delimiter="CRLF" />
      </Columns>
    </FlatFileFormat>
  </FileFormats>
  <Tables>
    <Table Name="Dst" SchemaName="Sandbox.[default]">
      <Columns>
        <Column Name="TableColumn1" DataType="String" Length="255" />
        <Column Name="TableColumn2" DataType="Int32" />
        <Column Name="TableColumn3" DataType="Int32" />
        <Column Name="TableColumn4" DataType="String" Length="255" />
      </Columns>
    </Table>
    <Table Name="Src" SchemaName="Sandbox.[default]">
      <Columns>
        <Column Name="TableColumn1" DataType="String" Length="255" />
        <Column Name="TableColumn2" DataType="String" Length="255" />
        <Column Name="TableColumn3" DataType="String" Length="255" />
        <Column Name="TableColumn4" DataType="String" Length="255" />
      </Columns>
    </Table>
  </Tables>
  <Packages>
    <Package Name="UNION_ALL" ConstraintMode="Parallel">
      <Tasks>
        <Dataflow Name="DataflowTask 1">
          <Transformations>
            <OleDbSource Name="OLEDBSource 1" ConnectionName="SandboxConn">
              <TableInput TableName="Sandbox.[default].Src" />
            </OleDbSource>
            <DataConversion Name="DataConversion 2">
              <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
              <Columns>
                <Column SourceColumn="TableColumn2" TargetColumn="dc.TableColumn2" DataType="Int32" />
                <Column SourceColumn="TableColumn3" TargetColumn="dc.TableColumn3" DataType="Int32" />
              </Columns>
            </DataConversion>
            <OleDbDestination Name="OLEDBDestination 3" ConnectionName="SandboxConn">
              <ErrorHandling ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />
              <TableOutput TableName="Sandbox.[default].Dst" />
              <Columns>
                <Column SourceColumn="dc.TableColumn2" TargetColumn="TableColumn2" />
                <Column SourceColumn="dc.TableColumn3" TargetColumn="TableColumn3" />
              </Columns>
            </OleDbDestination>
            <UnionAll Name="UnionAll 4">
              <InputPaths>
                <InputPath OutputPathName="DataConversion 2.Error" />
                <InputPath OutputPathName="OLEDBDestination 3.Error">
                  <Columns>
                    <Column SourceColumn="dc.TableColumn2" IsUsed="false" />
                    <Column SourceColumn="dc.TableColumn3" IsUsed="false" />
                  </Columns>
                </InputPath>
              </InputPaths>
            </UnionAll>
            <FlatFileDestination Name="FlatFileDestination 5" ConnectionName="FailOutputConn" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
</Biml>