retain null, blank, na values while excel to sql server data import in SSIS data flow task

0
votes

I would like retain null, blank, na values as it is when I import data from excel to SQL server in SSIS. I use data flow task to import data from excel to sql server. As mentioned it na values are converted to null or empty sometimes. Please help

1
sql-serverexcelssisdft
#N/A is not a value in Excel. It is an error. You can copy and paste special the sheet with values, before import to get the #N/A When choosing the data source, check the box Retain Null Values to keep NULL values intact - Raj
Thanks for the quick response. In my excel sheet i have values 'na' (Not applicable) but not #N/A . and some blank values. User wants to retain na , empty values, he doesn't want them to see as NULL values. I have selected the Keep NULL values checked in the SSIS Data Flow task Excel source. - user3744361
What is the datatype of the destination column? - Raj
Its numeric column in excel, i have it as nvarchar(255) in my sql server destination table. - user3744361

1 Answers

0
votes

Your issue seems to be that Excel connection manager recognizes the column as integer based on the default 8 row sample size. Once it is identified as integer, the remaining rows are treated the same and any values that cannot be converted to integer are treated as NULL.

After creating the connection in SSIS, select the connection and in the properties pane against Connection String, add IMEX=1 in the end.

Here is an example

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test\New Microsoft Excel Worksheet.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1 ";

Please do note that the IMEX argument isn't a cure-all. It's typically described (incorrectly) as "reading all values as text". What it does is instruct the Excel driver to go into "import mode", which instructs the driver to interpret columns that have numbers AND text to be imported as text only, instead of interpreted according to it's regular "guessing".