There is no such thing as a NULL cell in Excel. An empty cell in Excel is simply an empty string. Excel doesn't actually understand types at all. Everything is a string. What you're referring to as a "type" in Excel is just the formatting that is applied to a column/cell for display purposes and has no bearing on the actual string value. When SSIS imports the data is sees everything as strings. To make the import a little easier it tries to figure out what the actual type is by heuristics. If it cannot figure it out then it just assumes string. Based upon this document it currently uses the first 8 rows. If you have mismatched data then it won't be able to figure things out. Therefore the recommendation is to modify the
Extended Properties to include
IMEX=1 which tells the provider not to bother and everything gets imported as a string. You would then add a data transformation step to convert the textual values to the actual types you need using the standard SSIS tools. It is at this point you'd also be able to do conditional things like change empty text values to a default value if you wanted.