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:;en-us;194124

UPDATE: another great i found at 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 !