Adding Row Numbers Excel Sources in SSIS

This is an interesting challenge that I recently explored during a forum discussion on SQLServerCentral.com. The situation in which the user presented wasn't exactly ideal so I will first present the other two options I proposed besides the final one that appears to work for him in his environment.

Challenge: When extracting data from an Excel source I would like to add a row number.

Solutions:

1. Include an identity column in the staging table which would add the row number for future data flows that consume the data.
2. Write the Excel data to a transient table (which has an identity column) that gets truncated at the beginning of the package (resetting the identity column).

Both of these options are expensive I/O operations that would slow things down. Another option presented by Jack Corbet was to use a script task to increment a package variable row by row and adding that to the output.

The final solution I proposed seems to be suitable for this case and I may look to implement this more in my future SSIS requirements. Here goes:

1. Added an OLE DB Source to my data flow.
2. Selected a SQL 2005 DB Server for my connection manager.
3. Chose ‘SQL Command' as my Data access mode.
4. Used the RowNumber() function combined with the OPENROWSET() connection method to compose a query which pulled all the data in from the Excel sheet adding in the RowNumber.

In order for this to work your SQL 2005 Surface Area Configuration must allow the OPENROWSET() connection method and you'll most like want to define this SQL Statement dynamically through a package configuration or some other method.

Here is the query:

SELECT

                ROW_NUMBER() OVER (order by [VendorAccountNo]) AS [RowNumber]
        ,       [VendorAccountNo]
        ,       [LocalAccountNo]
        ,       [CompanyCd]
        ,       [CUSIP]

FROM

        OPENROWSET(
                'Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:tempMySheet.xls;HDR=0; IMEX=1;',
                'SELECT * FROM [Sheet1$]'
        )

I'll probably write more on this later as to which method provides the best performance and maintainability but for now feel free to comment on what you think…

Cheers!,
Ben