<Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Annotations> <Annotation> File: Add.RowNumber.biml Demo biml adding a rownumber to FileExport Enviroment: DB: MS-SQL2012 BIML: 1.6 VS2010 BIDS Helper (c) John Minkjan biml101.blogspot.com </Annotation> </Annotations> <FileFormats> <FlatFileFormat Name ="FFF AdventureWorks2012 Person Address" ColumnNamesInFirstDataRow="true" FlatFileType="Delimited" HeaderRowDelimiter=";" RowDelimiter="CRLF" TextQualifer="|"> <Columns> <Column Name="RowNumber" DataType ="String" Length="38" Delimiter =";"></Column> <Column Name="AddressID" DataType ="String" Length="128" Delimiter =";"></Column> <Column Name="AddressLine1" DataType ="String" Length="128" Delimiter =";"></Column> <Column Name="AddressLine2" DataType ="String" Length="128" Delimiter =";"></Column> <Column Name="City" DataType ="String" Length="128" Delimiter =";"></Column> <Column Name="StateProvinceID" DataType ="String" Length="128" Delimiter =";"></Column> <Column Name="PostalCode" DataType ="String" Length="128" Delimiter =";"></Column> <Column Name="rowguid" DataType ="String" Length="128" Delimiter =";"></Column> <Column Name="ModifiedDate" DataType ="String" Length="128" Delimiter ="CRLF"></Column> </Columns> </FlatFileFormat> </FileFormats> <Connections> <OleDbConnection Name="CnOleDBAdventureWorks2012" ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;" RetainSameConnection="true"> </OleDbConnection> <FlatFileConnection Name ="FF AdventureWorks2012 Person Address" FileFormat ="FFF AdventureWorks2012 Person Address" FilePath="C:\AdventureWorks2012.Person.Address.dat"></FlatFileConnection> </Connections> <Packages> <Package Name="Add.RowNumber" ConstraintMode="Linear"> <Tasks> <Dataflow Name="DFT Add Rownumber"> <Transformations> <OleDbSource Name ="ODS AdventureWorks2012 Person Address" ConnectionName ="CnOleDBAdventureWorks2012"> <DirectInput> SELECT [AddressID] ,[AddressLine1] ,[AddressLine2] ,[City] ,[StateProvinceID] ,[PostalCode] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2012].[Person].[Address] </DirectInput> </OleDbSource> <DerivedColumns Name="DC ADD RowNumber Column"> <Columns> <Column Name="RowNumber" DataType ="Int64">0</Column> </Columns> </DerivedColumns> <ScriptComponentTransformation Name ="SC Add RowNumber"> <ScriptComponentProject> <ScriptComponentProject Name="SC_AddRowNumber"> <AssemblyReferences> <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" /> <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" /> <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" /> <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" /> <AssemblyReference AssemblyPath="System.dll" /> <AssemblyReference AssemblyPath="System.AddIn.dll" /> <AssemblyReference AssemblyPath="System.Data.dll" /> <AssemblyReference AssemblyPath="System.Xml.dll" /> </AssemblyReferences> <InputBuffer Name="Input0"> <Columns> <Column Name="RowNumber" DataType ="Int64" UsageType ="ReadWrite"></Column> </Columns> </InputBuffer> <OutputBuffers> <OutputBuffer Name="Output0"></OutputBuffer> </OutputBuffers> <Files> <File Path ="AssemblyInfo.cs"> using System.Reflection; using System.Runtime.CompilerServices; [assembly: AssemblyTitle("SC_AddRowNumber")] [assembly: AssemblyDescription("")] [assembly: AssemblyConfiguration("")] [assembly: AssemblyCompany("")] [assembly: AssemblyProduct("SC_AddRowNumber")] [assembly: AssemblyCopyright("Copyright @ 2012")] [assembly: AssemblyTrademark("")] [assembly: AssemblyCulture("")] [assembly: AssemblyVersion("1.0.*")] </File> <File Path ="main.cs"> using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { Int64 lRownumber = 0; public override void PreExecute() { base.PreExecute(); } public override void PostExecute() { base.PostExecute(); } public override void Input0_ProcessInputRow(Input0Buffer Row) { lRownumber = lRownumber + 1; Row.RowNumber = lRownumber ; } } </File> </Files> </ScriptComponentProject> </ScriptComponentProject> </ScriptComponentTransformation> <FlatFileDestination Name ="FFD AdventureWorks2012 Person Address" ConnectionName ="FF AdventureWorks2012 Person Address" Overwrite ="true"> </FlatFileDestination> </Transformations> </Dataflow> </Tasks> </Package> </Packages> </Biml>
File: Add.RowNumber.biml Demo biml adding a rownumber to FileExport Enviroment: DB: MS-SQL2012 BIML: 1.6 VS2010 BIDS Helper (c) John Minkjan biml101.blogspot.com Also published on BIML 101: http://biml101.blogspot.nl/2012/09/biml-add-row-number-to-file-export.html
Comments
There are no comments yet.