SSIS is forcing Excel data types I don't want. Changing connection string isn't working.

Question

I am importing from an excel spreadsheet and it is not allowing me to change the source column type from Double to Unicode string.

I have tried using "IMEX=1;" in the connection string but this appears to be doing absolutely nothing.

The package refuses to validate and when i execute the package therefore will not run, and it keeps wanting to rest the "Input" of the external column to "Float" when i definately want it "Unicode", even though I've set "Validate" to false.

I must be missing something?!!?

Answer 1

There is no easy answer to this question.

Basically it is a known issue with SSIS. It autoreads a certain amount of records and decides that is the datatype, and you CANNOT change this (it will keep giving you Metadata errors and will not validate, and keep resetting it to what it has decided it thinks it is). You CAN set the number of records in the registry, but this doesn't solve the underlying problem, as sometimes the file you are importing may or may not contain strings.

I have worked around it by placing a single quote (') character in front of all the entries in the column that i want to be detected as a string. This means that whenever SSIS validates it will assume it is unicode, which is what I want.

Setting "IMEX=1" is only useful when SSIS actually detects alphanumeric records.

Cdonner: The source datatype matters because it is setting it to numeric when in fact the column may contain strings as well. So it will bomb out when it comes across a string, maybe 1000 rows in.

Sam: Excel 2003. I believe this is still an issue in SQL Server 2008 SSIS.