I understand your issues - Excel is probably my least preferred data source. Unfortunately, Excel does not really care too much about data types - underneath the wrappers, I suspect that each cell is just a bunch of bytes and the display format is used as the type that people think is stored in the cell.

The oledb provider needs to work with that sort of type-less data and struggles big time. What it actually does is parse the first few rows (by default 8 rows but this is configurable using a registry setting) to try to work out what data type to use. This is why you get different data types for a column depending on what is in the first few rows. Not very useful when SSIS really needs the data type to be the same each time otherwise it gets upset.