SSIS problems with Excel Source

I'm working on an import to my databases from an Excel sheet. This should have been a breeze with SSIS if not because of issues with the Excel Source data flow, which is apparently because of Jet OLEDB. Let me describe the issue. When data is read from an Excel worksheet, the Jet samples the first few rows for the data type, the problem is that when it encounters a different one other than what it assumed at first, instead of raising an exception, it just simply drops the data and replaces it with NULL. And so, what happened at first, I thought that all data was imported in the database not knowing that some of them were NULL. Bad...bad...bad.

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";

I added IMEX=1

2. Went to the registry of where the package will run and changed: Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows to 0 (zero for maximum).

This solution came from these posts:

msmvps
sqlteam
bi-polar23

Thank you all!