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.
The component is provided as an MSI file, however to complete the installation, you will have to add the transformation to the Visual Studio toolbox by hand. This process has been described in detail for the Trash Destination, just select Row Number Transformation in the Choose Toolbox Items dialog.
Download & Version History
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
