SSIS incorrectly sets string data as floating point when reading from Excel file

Question

I have an SSIS project that previously worked. It read a customer ID from an Excel file and copied it to a SQL server destination. The customer ID field was originally set to floating point when getting the input from the Excel file. Now there is a customer ID that starts with a "Z" then a number.

Whenever I change the External Column in the Excel Source Output for the customer ID to Unicode String [DW_STR] and save, I get an error notification. When I go back into it, it asks:

The component is not in a valid state. The validation errors are:
Error at Read madden file to work db [Excel Source [194]]: The output column "GM_CUSTOMER_ID" (3333) on the error output has properties that do not match the properties of its corresponding data source column.


Do you want the component to fix these errors automatically?

When I select yes, it just changes the customer ID back to a floating point. How can I change the datatype of the customer ID and correct this error?

UPDATE: After doing further research, it appears when a column has mixed data when read from an Excel file, the Jet Engine automatically tells SSIS that the datatype is whatever datatype is most prevalent. So, if I am importing data that is mostly floating point, it sets the datatype for the column in SSIS to floating point and sets all other values to NULL! I am trying to find a workaround.

what is the type of this field in your sqlserver's destination table? — Sergey Benner
– Sergey Benner, Commented Feb 8, 2012 at 16:10
The datatype in the destination table is string. I should also mention that between reading the Excel file and the destination table there is a data conversion to convert the data to corresponding sql server datatypes. — Jason Rae
– Jason Rae, Commented Feb 8, 2012 at 16:26

Community · Accepted Answer · 2017-05-23 12:03:32Z

1

This is a similar question to:

Help with a OleDB connection string for excel files

However, the only thing that worked for me was setting the TypeGuessRows value to a high number in the registry at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Feb 8, 2012 at 18:14

Jason Rae

2,6735 gold badges26 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

SSIS incorrectly sets string data as floating point when reading from Excel file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related