May 11, 2012

SSIS: Excel values import as NULL

SSIS Imports Values as NULL
When using SSIS to import data from an Excel Spreadsheet to SQL Server, some values in an Excel column might be imported as Null when the underlying value is not Null.
I had this occur with a zipcode column that contained intermixed numeric and text datatypes.
Excel ISAM driver limitation
According to Microsoft, this is caused by a limitation of the Excel ISAM driver. It determines the datatype of an Excel column by sampling the values in the first few rows of the column and then chooses the datatype for that column. Cells in the column that do not conform to the chosen datatype default to Null.
Updating the server registry key
I found that adding the option IMEX=1; to the Excel connection string does not always resolve this problem.
I had to edit the server registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet4\Engines\Excel and modify the registry setting from TypeGuessRows=8 to a larger value in order to ensure the Excel ISAM driver sets the column datatype to text.
T-SQL code
Use this T-SQL code once you've updated the server registry key: INSERT INTO [yourTable] SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:yourDirectoryyourFile.xls;IMEX=1;HDR=YES', 'SELECT * FROM [YourSheet$]')
IMEX options:
  • 0 = Export mode
  • 1 = Import mode
  • 2 = Linked mode (full update capabilities)