Add Rownumber to file export

gravatar

John Minkjan

I like to add a row number to the flat file exports. This helps a lot in the communication with business when a problem arrives. This is an example on how to add a row number:

published 09.23.12

last updated 09.23.12


Share

Tags

  • Rownumber
  • ScriptComponent
                            





<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

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.