Suppose you have excel and in that excel we
have a column that contains mixed data type (like number, date, string etc.).
While we are trying to load that in the database except integer all values
inserted as NULL.
My excel is having such type of data.
Let’s see when we are loading this data in
SSIS.
Do you know why this showing for string as
NULL values. Because while we mapping the product id in excel it is showing
data type as float.
For overcoming this issue we need to use
IMEX=1 property on the connection manager in excel configuration.
"IMEX=1;" tells the driver to
always read "intermixed" (numbers, dates, strings etc.) data columns
as text. Note that this option might affect excel sheet write access negative.
Let’s see how to changes this values in configuration.
Right click on the Excel connection manager.
You will get the properties windows.
In connection manager Type tab go to connection string
Edit it and add Extended Properties= “IMEX=1”
Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1"
|
When we have change see the data type changed as float to
string.
Now I am seeing the value
Now we are getting the value of the string data type as well
as float values.
