Add a Row Number Column with a Script Component Transformation
Loops over every user table in a SQL Server source and emits one extract package per table; each package reads the current max(rownumber) from the destination, runs the source through a Script Component transformation that adds an incrementing 'rownumber' column, and writes it to the destination table.