CODEWITHSUNDEEP
sql-serverssis
Jason Rae
Jason Rae

Reputation: 2663

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

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.

Upvotes: 0

Views: 2289

Answers (1)

Jason Rae
Jason Rae

Reputation: 2663

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

Upvotes: 1

Related Questions