SSIS - Excel Import Column with Strings and Numbers

While setting up an Excel Source, I was having an issue where I had a column with both strings and decimal number. If the Numbers were in the top rows, then it would only pull in the numbers. If only the strings were in the top rows, then it would only pull in strings and ignore the numbers.

Issue:

Excel looks at the first few rows to determine the column data type. And for some reason, when it decides a column is a string, it ignores all numbers in that column.

Solution:

In the Excel Connection Manager, under the Connection String, it will look something like this.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Files\FileName.xls;Extended Properties="EXCEL 8.0;HDR=NO";

You just need to add this code to the Extended Properties.

IMEX=1

So it now looks like this.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Files\FileName.xls;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1";

This command tells Excel that it has mixed data types in columns.

Found this solution here.