I regulary have to load some Excel sheets into a datawarehouse, what happens a lot is that some values in an Excel column may be returned as Null when the underlying value is not a Null. This typically occurs when numeric and text datatypes are intermixed within the same Excel column.
This appears to be a strange behavior of the Excel ISAM driver, when you load a column it reads the first 8 rows and tries to determine what it should load the data values as. Now if your data has a mix of characters and numbers, you need to make sure you reflect this in the first 8 rows or it will not load correctly. The official explanation and workarounds can be found at ms support: http://support.microsoft.com/default.aspx?scid=kb;en-us;194124
UPDATE: another great i found at http://developers.de/blogs/nadine_storandt/archive/2006/12/05/SQL-2005-SSIS-Excel-Source-Insert-NULL-Values-for-all-Records.aspx and you could do the following:
To fix this problem you have to add the following property to the Connection String of your Excel Connection Manager:
Excel 8.0;HDR=YES;IMEX=1
The property IMEX = 1 specifies that all datatypes should be import which are defined in the Excel Source.
Works great !
Actually, I found that you need to perform BOTH steps suggested by MS, otherwise just either one alone won’t cut it.
To summarize:
You *NEED* to include the “IMEX=1” parameter in the extended properties in the connection string AND move a couple lines with chars to the beginning rows of the excel spreadsheet.
Also need to ensure that “IMEX=1” has no spaces in it. Other than that I was amazed!