The Row Number Transformation calculates a row number for each row, and adds this as a new output column to
the data flow.
The column number is a sequential number, based on a seed value. Each row receives
the next number in
the sequence, based on
the defined increment value.
The final row number can be stored in a variable for later analysis, and can be used as part of a process to validate
the integrity of
the data movement.
The Row Number transform has a variety of uses, such as generating surrogate keys, or as
the basis for a data partitioning scheme when combined with
the Conditional Split transformation.
Properties
Property
Data Type
Description
Seed
Int32
The first row number or seed value.
Increment
Int32
The value added to
the previous row number to make
the next row number.
OutputVariable
String
The name of
the variable into which
the final row number is written post execution. (Optional).
The three properties have been configured to support expressions, or they can set directly in
the normal manner. Expressions on components are only visible on
the hosting Data Flow task, not at
the individual component level. Sometimes
the data type of
the property is incorrectly set when
the properties are created, see
the Troubleshooting section below for details on how to fix this.
Installation
The component is provided as an MSI file which you can download and run to install it. This simply places
the files on disk in
the correct locations and also installs
the assemblies in
the Global Assembly Cache as per Microsoft’s recommendations.
You may need to restart
the SQL Server Integration Services service, as this caches information about what components are installed, as well as restarting any open instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.
For 2005/2008 Only - Finally you will have to add
the transformation to
the Visual Studio toolbox manually. Right-click
the toolbox, and select Choose Items.... Select
the SSIS Data Flow Items tab, and then check
the Row Number transformation in
the Choose Toolbox Items window. This process has been described in detail in
the related FAQ entry for How do I install a task or transform component?
We recommend you follow best practice and apply
the current
Microsoft SQL Server Service pack to your SQL Server servers and workstations, and this component requires a minimum of SQL Server 2005 Service Pack 1.
Downloads
The Row Number Transformation is available for SQL Server 2005, SQL Server 2008 (includes R2) and SQL Server 2012. Please choose
the version to match your SQL Server version, or you can install multiple versions and use them side by side if you have more than one version of SQL Server installed.
Row Number Transformation for SQL Server 2005
Row Number Transformation for SQL Server 2008
Row Number Transformation for SQL Server 2012
Version History
SQL Server 2012
Version 3.0.0.6 - SQL Server 2012 release. Includes upgrade support for both 2005 and 2008 packages to 2012.
(5 Jun 2012)
SQL Server 2008
Version 2.0.0.5 - SQL Server 2008 release.
(15 Oct 2008)
SQL Server 2005
Version 1.2.0.34 – Updated installer.
(25 Jun 2008)
Version 1.2.0.7 - SQL Server 2005 RTM Refresh. SP1 Compatibility Testing. Added
the ability to reuse an existing column to hold
the generated row number, as an alternative to
the default of adding a new column to
the output.
(18 Jun 2006)
Version 1.2.0.7 - SQL Server 2005 RTM Refresh. SP1 Compatibility Testing. Added
the ability to reuse an existing column to hold
the generated row number, as an alternative to
the default of adding a new column to
the output.
(18 Jun 2006)
Version 1.0.0.0 - Public Release for SQL Server 2005 IDW 15 June CTP
(29 Aug 2005)
Screenshot
Code Sample
The following code sample demonstrates using
the Data Generator Source and Row Number Transformation programmatically in a very simple package.
Package package = new Package();
package.Name = "Data Generator & Row Number";
// Add
the Data Flow Task
Executable taskExecutable = package.Executables.Add("STOCK:PipelineTask");
// Get
the task host wrapper, and
the Data Flow task
TaskHost taskHost = taskExecutable as TaskHost;
MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
// Add Data Generator Source
IDTSComponentMetaData100 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
componentSource.Name = "Data Generator";
componentSource.ComponentClassID =
"Konesans.Dts.Pipeline.DataGenerator.DataGenerator, Konesans.Dts.Pipeline.DataGenerator, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b";
CManagedComponentWrapper instanceSource = componentSource.Instantiate();
instanceSource.ProvideComponentProperties();
instanceSource.SetComponentProperty("RowCount", 10000);
// Add Row Number Tx
IDTSComponentMetaData100 componentRowNumber = dataFlowTask.ComponentMetaDataCollection.New();
componentRowNumber.Name = "FlatFileDestination";
componentRowNumber.ComponentClassID =
"Konesans.Dts.Pipeline.RowNumberTransform.RowNumberTransform, Konesans.Dts.Pipeline.RowNumberTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b";
CManagedComponentWrapper instanceRowNumber = componentRowNumber.Instantiate();
instanceRowNumber.ProvideComponentProperties();
instanceRowNumber.SetComponentProperty("Increment", 10);
// Connect
the two components together
IDTSPath100 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], componentRowNumber.InputCollection[0]);
#if DEBUG
// Save package to disk, DEBUG only
new Application().SaveToXml(String.Format(@"C:\Temp\{0}.dtsx", package.Name), package, null);
#endif
package.Execute();
foreach (DtsError error in package.Errors)
{
Console.WriteLine("ErrorCode : {0}", error.ErrorCode);
Console.WriteLine(" SubComponent : {0}", error.SubComponent);
Console.WriteLine(" Description : {0}", error.Description);
}
package.Dispose();
Troubleshooting
Make sure you have downloaded
the version that matches your version of SQL Server. We offer separate downloads for SQL Server 2005, SQL Server 2008 and SQL Server 2012.
If you get an error when you try and use
the component along
the lines of
The component could not be added to
the Data Flow task. Please verify that this component is properly installed. ...
The data flow object "Konesans ..." is not installed correctly on this computer, this usually indicates that
the internal cache of SSIS components needs to be updated. This is held by
the SSIS service, so you need restart
the the SQL Server Integration Services service. You can do this from
the Services applet in Control Panel or Administrative Tools in Windows. You can also restart
the computer if you prefer. You may also need to restart any current instances of Business Intelligence Development Studio (BIDS) / Visual Studio that you may be using to build your SSIS packages.
Once installation is complete you need to manually add
the task to
the toolbox before you will see it and to be able add it to packages - How do I install a task or transform component?
Please also make sure you have installed a minimum of SP1 for SQL 2005.
The IDtsPipelineEnvironmentService was added in SQL Server 2005 Service Pack 1 (SP1) (See http://support.
microsoft.com/kb/916940). If you get an error Could not load type '
Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly '
Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. when trying to open
the user interface, it implies that your development machine has not had SP1 applied.
Very occasionally we get a problem to do with
the properties not being created with
the correct data type. Since there is no way to programmatically to define
the data type of a pipeline component property, it can only infer it. Whilst we set an integer value as we create
the property, sometimes SSIS decides to define it is a decimal. This is often highlighted when you use a property expression against
the property and get an error similar to Cannot convert System.Int32 to System.Decimal. Unfortunately this is beyond our control and there appears to be no pattern as to when this happens. If you do have more information we would be happy to hear it. To fix this issue you can manually edit
the package file.
In Visual Studio right click
the package file from
the Solution Explorer and select View Code, which will open
the package as raw XML. You can now search for
the properties by name or
the component name. You can then change
the incorrect property data types highlighted below from Decimal to Int32.
<component id="37" name="Row Number Transformation" componentClassID="{BF01D463-7089-41EE-8F05-0A6DC17CE633}" … >
<properties>
<property id="38" name="UserComponentTypeName" …>
<property id="41" name="Seed" dataType="System.Int32" ...>10</property>
<property id="42" name="Increment" dataType="System.Decimal" ...>10</property>
...
If you are still having issues then contact us, but please provide as much detail as possible about error, as well as which version of
the the task you are using and details of
the SSIS tools installed.
