SSIS Excel Import Forcing Incorrect Column Type

Question

I'm trying to import a spreadsheet to our database using SSIS. For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I've tried remapping the columns to be nvarchar(255) but it still doesn't want to select the data it thinks is double, because there are characters in it. If I try to edit the SSIS package and change the column types in the Excel Source, it won't let me change the type of the columns in the Error Output and gives me an error if the regular output and error output columns don't match.

Why is SSIS insisting that these columns are Double? How can I force it to realize these are strings? Why does everything from microsoft have to not quite work correctly?

EDIT: I found this: http://support.microsoft.com/kb/236605

I sorted my data so that mixed data types would be at the top, and guess what: The problem reversed. Instead of not importing character data, it stopped importing purely numeric data. Apparently someone doesn't think 12345 can be represented as a string...

Answer 1

You can also alter the registry to look at more values than just the first 8 rows. I have used this method and works quite well.

http://support.microsoft.com/kb/281517

Answer 2

Well IMEX=1 did not work for me. Neither did Reynier Booysen's suggestion. (I don't know if it makes a difference but I'm using SQL Server 2008r2). A good explanation of some workarounds and also some explanations of why IMEX=1 is limited to the first eight rows of each spreadsheet can be found at http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/78b87712-8ffe-4c72-914b-f1c031ba6c75

Hope this helps

Answer 3

One thing that isn't mentioned in the accepted answer is that the "IMEX=1" parameter has to go inside the quoted portion of:

...;Extended Properties="...";

Answer 4

I've used the following recipe:

1. Import data from Excel to Access
2. Import data from Access to SQL Server

and it worked for me...