Excel Values Returned as NULL when the underlying Value Is Not NULL using Excel as Source in SSIS

Excel Values Returned as NULL  when the underlying Value Is Not NULL using Excel as Source in SSIS

When connecting to an Excel Source using SSIS, 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 problem is caused by a limitation of the Excel ISAM (Indexed Sequential Access Method) driver in that once it determines the datatype of an Excel column, it will return a Null for any value that is not of the datatype the ISAM driver has defaulted to for that Excel column. The Excel ISAM driver determines the datatype of an Excel column by examining the actual values in the first few rows and then chooses a datatype that represents the majority of the values in its sampling.

Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

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. 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.

 

Example 

  1. Our Original Excel data

  

2. Data became NULL when we used original Excel source in SSIS 

 Now Excel Connection Manager ->Connection String property is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Job pynadath\Desktop\CWP.xls;Extended Properties="Excel 8.0;HDR=YES";

How To Solve This

1.      Select the properties of Excel Connection manager of Excel Source

2.         Add IMEX=1 to the value of Extended Properties in the connection string

3.       Save

Now Excel Connection Manager ->Connection String property is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Job pynadath\Desktop\CWP.xls;Extended Properties="Excel 8.0;HDR=YES ;IMEX=1";

Note: If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.
Select the properties of Excel Connection manager of Excel Source
 Then We need to Following 
 1) On the Start Menu 
 2) Type regedit Click Ok
 3) In the Registry Editor , Expand the following Key depending on the version of Excel that you are running. HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel

 Right Click on "TypeGuessRows" and Select Modify and change the value of HExadecimal to 0 Or change  the value of Decimal to 1.

4) Click Ok.