Unable to preview SSIS Excel source data after skipping first few rows

Question

I am using SQL Server 2008 BIDS. I am trying to read in an Excel file, having multiple sheets. The names are mostly alphabetical( and few with special char '&'). The data starts at row 8. I have skipped the blank rows by setting the rows and columns in the open rowset property for the Excel source. I get the exact mappings. However, I am not able to preview the data. The package runs successfully(everything turns green), but there is no data in my destination.

The error I receive while I try to preview is:

There was an error displaying the preview.

Additional Information:

Index and Length must refer to a location within the string. Parameter name: Length(mscorlib)

Please let me know if I am doing anything wrong or I am missing any settings.

The links I have referred to:

Skipping rows when importing Excel into SQL using SSIS 2008

https://connect.microsoft.com/SQLServer/feedback/details/557049/ssis-fails-to-preview-excel-source-connector-due-to-incompatible-sheet-name

Thanks

Answer 1

I cracked it with the help of one of my friend .

In the properties of the Excel Source >> Custom Properties >> Open Rowset >> SheetName$A12:J

It means skip the rows till A12. And the data is taken into account from A12 though end of J column.

Problem solved.