<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Annotations> <Annotation> File: UnionAllComponent.biml Demo Biml using Union All component to combine error output and remove columns from the dataflow buffer that are not needed. Enviroment: DB: Sandbox BIDS Helper: 1.6 VS2005, VS2008 or VS2010 BimlEngine: 8/14/2012 11:11 PM Author: Paul S. Waters, http://bimlscript.com/Author/Details/paul.waters%40varigence.com </Annotation> <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>
Using the Union All component
Paul S. Waters
Demo Biml using Union All component to combine error output and remove columns from the dataflow buffer that are not needed.
published 01.03.13
last updated 01.03.13
Comments
There are no comments yet.