Problem : While loading data from Excel source to SQL destination, I was facing problem that source column contains both Numeric and Alphanumeric type data. While loading this data to SQL, some times values are populated as NULL in SQL instead source column value. I have spend whole day to find out solution for this problem and finally following is the solution I found.

Solution : Set ConnectionString property of Excel source file as following

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=filepath\filename.xls;Extended Properties=”EXCEL 8.0;HDR=YES;IMEX=1“;

NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.

Reference : http://support.microsoft.com/kb/194124/en-us