Excel Data Uploading problem using SSIS (Mixed Type Column)

Scenario

The source column of Excel source used to upload to SQL destination contains both Numeric and Alphanumeric type data. While loading this data to SQL, sometimes values are populated as NULL in SQL instead source column value.

Solution
1. In SSIS, set ConnectionString property of Excel source file as following

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

2. Modify the registry settings as follows:
- ImportMixedTypes = Text
- TypeGuessRows = 0

To change the setting for the TypeGuessRows or ImportMixedTypes value, follow these steps:
1. Close any programs that are running.
2. On the Start menu, click Run. Type regedit and click OK.
3. In the Registry Editor, expand the following key depending on the version of Excel that you are running:

Excel 97
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel

Excel 2000 and later versions
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

4. Select TypeGuessRows (or ImportMixedType) and on the Edit menu click Modify.
5. In the Edit DWORD Value dialog box, click Decimal under Base. Type a value between 0 and 16, inclusive, for Value data. Click OK and quit the Registry Editor.

NOTE: For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is very large. When this value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column.

Further explanation

The relevant registry keys (for Jet 4.0)
1. ImportMixedTypes
Value: Text = the data type for the whole column will be ‘Text’ data type (for Jet), VARCHAR(255) (for DDL), and adWChar (for ADO)

Value: Majority Type = a certain number of rows (more on this later) in each column are scanned and the data types are counted
(Both a cell’s value and format are used to determine data type)

2. TypeGuessRows (how many rows are scanned for each column)
Value : 0-16 (decimal) (0 = all rows)

IMEX (IMport EXport mode) in a connection string
1. IMEX=0 and IMEX=2: ImportMixedTypes being ignored and the default value of ‘Majority Types’ is used
2. IMEX=1: ImportMixedTypes=Text is honored

Source:
1. http://support.microsoft.com/kb/189897
2. http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/
3. http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/