Problem
Sometimes Data Flow source does not contain an ID column, or a number indicating a row number, which you need in further steps. To solve this, you may use a free ZappySys PowerPack component Template Transform. So let’s take a look at how to accomplish that.
Tip
Add Template Transform
Go to the Data Flow, drag and drop Template Transform from SSIS toolbox, and connect it right after the source, which does not provide ID/row number:
Configure Template Transform
Template Transform will provide the row number we want. So configure it like this: in the text area enter <<FUN_SEQUENCE>> and optionally you can give this column a name, e.g. RowNumber.
Configure Template Transform to get row numbers for each row in SSIS
By default Template Transform’s output type is DT_NTEXT. So if you wish to change the type to, for example, integer, use the Derived Column connector and CAST function to change it, e.g.:
(DT_I4)(DT_WSTR,100)RowNumber
Derived column added to convert DT_NTEXT column to an integer type.
Result
In the data viewer you would see that you have a column with a different row number in each row:
Results: How to add row numbers to rows in SSIS data flow
