I have a package that imports excel data into a SQL table. One of the columns in the table is of "smallint" data type and it allows for nulls. The excel file column that has the data that is suppose to go into this column will always contain either small int or in some cases "NULL".
I have noticed that when my source excel file has a NULL in this particular column I get the following error
There was an error with input column "Copy of Place of Service" (8582)
on input "OLE DB Destination Input" (8459). The column status returned
was: "The value could not be converted because of a potential loss of
data.".
If my source excel file only contains small int values then the package runs fine. I am not sure how I can fix this problem. I have tried changing data types in the Physical table as well as using different DataTypes (via the DataConversion component in SSIS) and I still keep getting the same error message.
Does anyone have any ideas of how I can get around this issue? Even though the column in the SQL table is checked to allow NULL I guess it is reading the NULL from the Excel source file as text or string and not as an actual NULL. Any suggestions?
Best Answer
My original answer was working (changing int to nvarchar) but I ran into another column which contained dates in the excel source file where some cells contained the string "NULL" (I did not want to have dates in a NVARCHAR column.) When SSIS got to this date column it was generating an error because it could not convert the string "NULL" to a date. It was not properly reading the "NULL" as a NULL value, instead it was reading it as a string. The way I was able to resolve this issue is by adding a derived column component to the package that replaced the date field by using the following expression
this is just an IF statement that will change the string "NULL" to an actual NULL value. The date is then passed thru a data conversion component and converted into DATETIME format.