Search code examples
ssisssis-2012ssis-2008

While Import Data from Excel to SQL DB SSIS Shows NULL for Interger Columns

I am using an Excel file to import data in the SQL DB Table.

My column contains Both Text and Interger data types.

While Previewing my Excel file in SSIS Excel Source Assistant it shows NULL for that Integer Rows.

Note: I don't need change any columns in the Excel file.

Can we achieve this in SSIS itself?

enter image description here

enter image description here

Solution

  • The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type.

    So, regarding this issue, this behavior is by design. To address this issue, we add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

    For .xlsx file, the connection string of the Excel Connection Manager is like: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\Test.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1";

    For .xls file, the connection string of the Excel Connection Manager is like: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Test.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";