I discovered some undesirable behavior with the SSIS  Excel Data Source.  Although this issue has been well-documented for a while, I thought it is worth mentioning again.

I had one column in my spreadsheet that was sparsely populated, nearly all blank values.  When I ran my SSIS package to extract the data from the spreadsheet, every row in that column was set to NULL including those that had a valid value in the spreadsheet.

Upon further investigation, I discovered the issue stems from the Jet driver.  Apparently, by default, only the first 8 rows of data are scanned to determine the data type for each column.  If the first 8 rows of data in a column are blank, then any valid data in the additional rows is ignored and treated as NULL.  It is possible to change the value in the registry so more than 8 rows are scanned.

In addition, I also discovered that if you use the Excel data source and want to run your package from BIDS, then ‘Run64BitRuntime’ in the solution properties must be set to False.  You can configure this as follows:

Right click on the solution in BIDS and select ‘Properties’.

Set Run64BitRuntime to False.