1

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

Improve this question

5 Answers 5

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

1 Comment

I was having all sorts of issues with the Excel Source until I tried this. Worked like a charm and not an obvious solution.
0

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
Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.