SSIS receive Excel column as DT_IMAGE

Question

Good day to you, Experts.

I'm stuck on a problem I'm having with an Excel 97-02 .xls file. When adding it as a source in SSIS, I'm getting an External Columns Datatype of DT_IMAGE . The column represents an ID and is numeric only. I can't extract and work with the data because of the DT_IMAGE datatype. Setting IMEX=1 didn't help.

Thank you in advance.

Answer 1

Reading Excel files in SSIS is done using OLEDB provider which may not detect the appropriate Excel column type.

There are many other questions mentioning similar issues such as:

• SSIS Excel Import Forcing Incorrect Column Type
• SSIS Excel Data Source - Is it possible to override column data types?
• SSIS keeps force changing excel source string to float

As you mentioned in the question, if you added ;Extended Properties="IMEX=1" to the connectionstring with no luck then i think there is 4 things you can try:

1. Sorting column data inside Excel
2. Change the entire column formatting manually
3. Go to the advanced editor on the Excel source >> into the output column list and set the type for each of the columns.
4. Adding IMEX=1; MAXROWSTOSCAN=0 to the connectionstring
5. If nothing of the above steps worked then you should save the Excel sheet as a text file and then you use Flat File Connection manager