Been able to add an auto incrementing row number using the Audit Data Task would be very useful. The only workaround I can think of is to use two select statements: one that that used the IDENTITY function and inserts data into a temp table and another that retrieves data from the temp table. This workaround has two limitations:
1 – If temp tables are used the OLE DB data source cannot find any column names returned – this is related to ID 285498. A workaround is to use SET FMTONLY OFF before the statements, but if SET FMTONLY OFF parameters cannot be specified.
2 – It can only be used with relational data sources. When handling Flat Files it would be very useful to now the exact row number of any ‘offending’ rows