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

I've seen this issue before, it's Excel that is the issue not SSIS. Excel samples the 1st few rows and then infers the data type even if you explicitly set it to text. What you need to do is put this into the Excel file connection string in the SSIS package. This instruction tells Excel that the columns contain mixed data types and hints it to do extra checking before deciding that the column is a numeric type when in fact it's not.

;IMEX=1;

It should work with this (in most cases). The safer thing to do is export the Excel data to tab delimited text and use SSIS to import that.

• http://blogs.acceleration.net/ryan/archive/2005/01/11/477.aspx

Answer 2

You can convert (ie. force) the column data to text...check out my answer to this other question for steps describing how to do this:

http://stackoverflow.com/questions/779754/import-data-wizard-does-not-like-data-type-i-choose-for-a-column/779956#779956

Answer 3

I have the same issue. I tried to set IMEX = 1 and even change the GuessRow = 0 to let it goes through all the row to guess the data type. The mixed data loaded in now but with some unexpected results. numbers like 2345.37 is loaded as 2345.369999999999 if most of the rows are numeric. In my situation 2345.37 means account number instead of numeric amount so converted result like 2345.369999999 is not acceptable.

It would be very appreicated if any help about how to fix this.

Jenny

Answer 4

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 5

IMEX =1 in Extended Properties is Helping only if the first few values in the column are text. If the first few values in the column are numbers, the following text is not accepted by SSIS. WHAT TO DO NOW !!!

We cant ask user to sort data etc crap.. Or enter only text data... by making 30 as 30 units... They are not buying that there could be an issue with SSIS and EXCEL.. Its hopeless.. !!

Could you please help

Answer 6

Option 1. Use Visual Basic to iterate through each column and format each column as Text.

Use the Text-to-Columns menu, don't change the delimination, and change "General" to "Text"

Answer 7

IMEX=1

Will not help you if you have cells with more than 255 characters Eventually you will realise that you cant load the data without modifying the Excel file

BTW the idea of automation is to be able to load any file without manual intervention

There one solution which works... More Information

http://www.dbsoftlab.com/etl-tools/advanced-etl-pocessor-news/say-no-to-typeguessrows0-imex1-and-excel-odbc-bugs.html