I'm just getting confused today, because I tried to import Data from a Excel Source, but one column contains only NULL Values, but in the Excel Source there are a few entries for this column.
So the problem was SSIS Excel Source only look at the first 8 Rows if they contain the same value SSIS decide to use the same data type for all entries.
To fix this problem you have to add the following property to the Connection String of your Excel Connection Manager:
The property IMEX = 1 specifies that all datatypes should be import which are defined in the Excel Source.
Dec 05 2006, 12:11 PM