Sql-server – SSIS and Excel import

To use an Excel file as a source you need to define a named range containing the cells with data (that's Step 1 of the instruction you referenced in the question).

All the named ranges found in the file will be shown in the Select tables pane. After you select a table (i.e. a range) and click OK, a new source will be created.

You can also consider converting the Excel spreadsheet to the CSV format and using simple flat files as a source. If you're in a Windows environment and have have Excel installed, there's a VBS script that can help you automate this process.

^{I tested this in SSIS from a 2008 R2 install and Excel 2010.}

Using the Excel 97-2003 output format in the connection manager, I was able to reproduce the case where there was the header name in the first row, then a blank, then the data. But changing the "First row has column names" had a different effect for me: I still got the header row, but no blank.

Using the Excel 2007 format, I wasn't able to suppress the header row either, though in both cases, it didn't output a blank row.

You didn't say if you want the header row or not, or which output format you're using; based on my testing, you're using 97-2003, so all you might have to do is switch to the 2007 format.

Attaching a Data Viewer just before the Excel Destination shows only data rows, so the extra blank is being added by the Destination component. So if the Destination isn't configurable to get rid of the blank, you're out of luck.

Suffice to say, though, since the behaviour of this component seems very buggy and the whole setup process to even get this to work at all was such a pain, I would strongly consider switching to a more stable component like the Flat File Destination to output CSV, which you can open directly in Excel, too.