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.

Answer 2

I was getting this same error trying to import a .xlsx file into SSIS 2008. I first saved the file as .xls (Excel 97-2003). I was still getting the above error and couldn't preview the data with the new file.

In case this link breaks in the future: https://connect.microsoft.com/SQLServer/feedback/details/557049/ssis-fails-to-preview-excel-source-connector-due-to-incompatible-sheet-name

Once I removed the space from the Sheet name, I was able to preview the data. The tab was originally called Time Data; I changed it to TimeData.

Some other potential problems with the sheet name, according to the above site:

• Sheets named with all numbers (ex: 4385)
• Sheet names beginning with a number

Answer 3

After trying all the other answers with no luck, I renamed the spreadsheet. In the Excel connection manager, I browsed to the renamed spreadsheet and unchecked first row contains column names. I was then able to view the data in the preview window.

Answer 4

In my case I was trying to export from SQL into an Excel file and receiving "Index and Length must refer to a location within the string" while trying to preview the destination data. Removing the space from the Excel Sheet in the Excel Destination Editor fixed it for me.

Answer 5

The way to solve this is to use q SQL command in the data access mode.

Select * FROM [Sheet1$A20:K]

This will read the data correctly and no preview errors will occur.