SQL Server 2005 Transferring SSIS SQL Data to Excel Distorts Excel File

Question

I have an SSIS package that exports data from a table in a SQL Server 2005 database to an Excel spreadsheet. There are 15 sheets in this spreadsheet, and I'm trying to fill in the values ​​on one of these sheets.

I have a template file that I cloned to be an export target (a simple file system task). This template file is valid and not corrupted.

Then I have a data flow task that uses SQL Query as a data source. If I look at it, everything will be all right. Then I exclude everything except one of the columns (int data type).

I have an excel data source (which is the target table, as mentioned above) and I am linking them by wrapping one selected column. When I look through, I get the first hint that something is wrong:

An error occurred displaying the preview.

ADDITIONAL INFORMATION:

The index and length must refer to the location within the string.

Parameter Name: length (mscorlib)

When I run the package, it goes fine, reporting that I have 1 line copied to another. When I open the spreadsheet, I get the following error:

File error: data may be lost

However, the spreadsheet is still loading, and when I go to the sheet I'm trying to fill, he created a new column with text from another sheet.

When I try to use the entire column list in the SSIS package, it seems to be extracting data from other areas of the spreadsheet, which makes me think that the process is somehow distorting the spreadsheet.

My initial thought was that it might have been a problem with the service pack not being deployed, but I observed this behavior when installing RTM and SP3 SQL 2005 and got a little stuck.

I would be very grateful to everyone who had similar experience and could help. Thanks

Answer 1

We got to that.

To get the data in the correct format, the spreadsheet designer had a hidden row of values ​​as the first row of the table. This is what you need to do if you want your numbers to be numbers, etc.

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

The latter was not strictly necessary, and when they were deleted, the spreadsheet seemed to start working.

The peculiarity of this, however, is that the spreadsheet worked fine in all delivery environments. Only with local development did a problem arise.

If someone can shed light on why this is so, then it will be very grateful.

Answer 2

I had the same error in the preview:

"The index and length must refer to the location within the string.

Parameter Name: length (mscorlib) "

One of the sheets in the Excel file contained a white space, as well as a hyphen. It doesn't matter which sheet had a space or a hyphen, since I had to delete all of them in order to use the preview.

Answer 3

I have an excel data source (which is the target table, as mentioned above) and I am linking them by wrapping one selected column. When I look through, I get the first hint that something is wrong:

Preview error.

ADDITIONAL INFORMATION:

The index and length must refer to the location within the string.

Parameter Name: length (mscorlib)

When I run the package, it goes fine, reporting that I have 1 line copied to another. When I open the spreadsheet, I get the following error:

File error: data may be lost

Have you tried to recreate this destination component? That would be nice to start with, it looks like he became corrupt (not sure if corrupt is the right word, but in SSIS I found) this happens very often. I usually try to replace the components of a problem before exploring other possibilities.

Answer 4

This message appears to me when I try to preview an Excel file preview using SSIS.

The solution I found is to change the name of the sheet.

It should begin with a letter, and you need to remove all spaces.