Couple of days ago while importing data from an Excel file to a SQL Server table using SSIS I ran into an interesting issue. I found out that some of the data in the Excel file were inserted as NULL in the SQL Server table. It was a bit confusing to start with, I thought I was doing something wrong but after a few checks I was convinced there is something behind the scenes which was causing this problem and there indeed was.

Issue:

SSIS inserts NULL value into a table even when there is good data in the corresponding cells in the excel source.

Reason:

SSIS uses the Jet driver under the hood to read data from an Excel file. During the import process the JET driver looks into the first 8 rows on each column to decide the corresponding data type. In my scenario the column was actually of type double; however, the first eight row was empty, so the JET driver treated that column as String data type or varchar. 

So, during the import process when the driver encountered a double data type, it decided that its a wrong type and it just silently ignored it and stored a NULL value in the table. If the column which you are inserting into does not allow nulls then the transformation will fail.

Fix:

There is a fix for this. The JET driver connection string has configuration named "Extended Property", this configuration has a flag name IMEX, if we set this to 1 then it instructs the driver to always read "intermixed" data as text. Intermixed mean columns that may contain numbers, strings etc data types. The connectionstrings.com website has the details on the different settings.

In SSIS you can access the ConnectionString property by clicking the Excel Connection Manager and then look for ConnectionString in the Properties window as shown in the image below

Set your connection string property similart to below:

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