Row Number Transformation

The Row Number Transformation calculates a row number for each row. It offers ROW_NUMBER or IDENTITY like behavior, or surrogate key generation within the pipeline. The new row number and is added as a new output column to the data flow. The column number is a sequential number, based on a seed value as set by property or expression. The increment value can also be set similarly via the property or an expression. 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.

The Row Number Transformation is available free of charge from SQLIS.com, our free community resource site for SQL Server Integration Services.

Please note that our free resources are still subject to copyright and usage is subject to acceptance and bound by the accompanying license.

For more information and downloads, see Row Number Transformation on SQLIS.com