SQL Server 2005 SSIS Data Transfer SQL to Excel Corrupts Excel File

Question

I have an SSIS package that exports data from a table on a SQL Server 2005 database to an Excel Spreadsheet. The spreadsheet in question has 15 sheets, and I am trying to populate values on one of these sheets.

I have a template file which I clone to be the target for the export (a simple file system task). This template file is valid, and is not corrupt.

I then have my data flow task, which uses a SQL Query as the data source. If I preview this, everything is fine. I then elimine all but one of the columns (an int data type).

I have an excel data source (which is the target spreadsheet as mentioned above), and I link the two, carrying across the single column selected. When I preview, I get the first hint that something is wrong:

There was an error displaying the preview.

ADDITIONAL INFORMATION:

Index and length must refer to a location within the string.

Parameter name: length (mscorlib)

When I run the package, it goes through ok, reporting that I have 1 row copied across. When I open the spreadsheet, I get the following error:

File Error: data may have been lost

The spreadsheet still loads however, and when I go to the sheet I have been attempting to populate, it has created a new column with text from a different sheet.

When I try with the entire list of columns in the SSIS package, it appears to pull data from other areas of the spreadsheet, which leads me to believe that the process is somehow corrupting the spreadsheet.

My initial thought was that maybe this was an issue with the fact that a service pack had not been deployed, but I have observed this behaviour on a SQL 2005 RTM and SP3 installs, and am a little stuck.

I would be very grateful to anyone that who has had similar experiences and might be able to help. Thanks

Answer 1

We got to the bottom of this.

In order to get the data appearing in the correct format, the spreadsheet developer had a hidden row of values as the first row of the spreadsheet. This is something that you have to do if you want your numbers to be numbers, etc.

So, a 0 had been placed in cells whose column was to be numeric, a date in each cell of a date column, and an apostrophe (') for all the text columns.

The latter wasn't strictly necessary, and when they were removed, the spreadsheet appeared to start working.

The peculiarity of this, however, is that the spreadsheet worked ok throughout the shipping environments. It was only during local development that then problem occurred.

If anyone could shed any more light on why this is, then it would be much appreciated.

Answer 2

I had the exact same error in the preview:

"Index and length must refer to a location within the string.

Parameter name: length (mscorlib)"

One of the sheetnames in the Excel file contained a whitespace character as well as a hyphen. It didn't matter which sheet had the whitespace or hyphen as I had to remove all of them in order to use the preview.