Raj,

If you add that expression, you will indeed not have a NULL in the column, but that doesn't help the case where there was actual data in the column that was lost because the JET driver looked at the column and treated it as a FLOAT instead of a CHAR(255). The best example I have for this is Zip+4 codes mixed with Zip codes in a single column. Excel will read the column, see that the majority are FLOAT and will treat anything with a hyphen as a NULL for purposes of import because it's not a FLOAT. That defeats the purpose.

JW,

That solution looks okay, but as one person commenting on it noted, it forces everything to a char(255) and you lose any Memo data that might have been stored. If one of your columns is > 255 characters, you lose the data. You also then have to deal with everything that is now being treated as a char(255) and any odd conversions that happen as a result. That in itself shouldn't be too bad, but it is a pain. It's also kind of frustrating because you can't set that option when using the Wizard. You need to code for it. 😛

As noted in one of my earlier posts, I deal with this when receiving files from customers that have multiple sheets (making it impractical to try to export and choose a good delimiter), a mixture of data-types, and usually with some Note/Memo data. That leaves me with a lot of pain points when trying to load data directly from Excel. And there are a lot of workarounds, but hopefully people will be aware that there are definitely some areas to watch for when importing Excel using SSIS.