SSIS Issue with Excel source

I've been doing a bit of SSIS (the data transformation technology formally know as DTS), and came across a nasty issue with using an Excel source to import data. The problem may not actually lay with SSIS - the Excel Source in SSIS uses Jet under the hood, and the problem could rest entirely with Jet.

The problem occurs where you have a sparsely populated column - SSIS/Jet will only sample a certain number of rows (I think it is configurable in the registry somewhere) to determine the type of the data held in the column. Say, for arguments sake, only 20 rows are sampled and no data is found. The type of the data will default to a string. If row 21 contains a numeric type or a date, this row will be silently ignored and treated as a null. This is a pretty crappy default behaviour - silently ignoring data that you are spending a lot of time (and clients money) to import. You have been warned!

Anyhow, here's the solution, enjoy:

1. Going to the Excel Connection Manager, I changed the Connection Strings to: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\BillingReport\Upload.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1"