There are cases when you don't have a chance to add an auto-incrementing column to a source of a data flow. Sometimes it's not possible (for example, using a flat file source), sometimes it doesn't make a sense (when the data will be resorted or filtered in the data flow later).
I just uploaded a small example to TechNet Gallery how you can add an auto-incrementing column in the middle of a data flow. It's an SSIS 2008 R2 package - if you need another version, let me know via the Gallery. The package adds two columns to the data flow with Script Components - the sample codes are the same, just written in C# and VB.NET as well.
The package also shows how to use a recursive CTE (in the data source) to generate 500 rows of data. You will need to have a tempdb on your localhost, or to edit the connection manager to refer to any accessible SQL 2008 database.
Ratings, comments and questions are always welcome.
