Import Excel in SSIS and get Null values in stead of Numeric values

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 !

  • Werner Moecke

    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!