

Importing Excel files with mixed data types: Why some values are converted to NULL and how to get around that problem
Sample data to illustrate what happens with mixed data types during import:

Table results after importing file:
Values turned to NULL due to Majority Rule on Row Sampling that determines data type:

Row sampling based off registry setting “TypeGuessRows=#”
- TypeGuessRows # defaults to 8 with a max of 16
- Setting TypeGuessRows = 0 will scan all rows (per this article)
- Altering registry is obviously risky and rarely permitted (particularly on servers)
- TypeGuessRows = 0 would not be a good option in general
- Even if you set the max to 16 — any values that follow and don’t conform to the data type defined in the first 16 rows will all be imported as NULL
Enter IMEX
SSIS ConnectionString property: Extended property setting of IMEX
IMEX modes: 1 is Import , 0 is Export, 2 is Linked
All illustrations are in Import Mode

IMEX bypasses Majority Rule so that all values become text — as long as there is some mix of types within the row sampling:

But if IMEX only applies to the Row Sampling registry setting (TypeGuessRows = #) — what do you do about the rows that follow (9 and beyond below)?
Results in the following whether IMEX is used or not

IMEX Workaround for the above
Dummy row

Additional Material:
http://microsoft-ssis.blogspot.com/2011/06/mixed-data-types-in-excel-column.html