I've been trying to import data from an Excel spreadsheet using DTS. I've been having problems with sparsely populated columns. Basically if the first eight rows are blank it ignores any other values in the following rows. This is because the Excel driver is only examing the first eight rows to determine data types and such. There's a registry entry you can change to force the driver to search the entire spreadsheet before making any data type determinations. I've got mine set to “0” which scans the entire spreadsheet. I haven't had any performance problems and it's fixed a number of annoying problems.
This is a companion discussion topic for the original entry at http://billg.sqlteam.com/2005/02/04/4116/