SSIS Data Flow Task Excel to SQL table NULL value will not work for small INT datatype

Question

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?

Answer 1

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

(DT_WSTR,255)Auth_from_date == "NULL" ? NULL(DT_WSTR,50) : (DT_WSTR,255)Auth_from_date

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.

Answer 2

I was able to get around this issue by Changing the data type of the destination table column to nvarchar and then doing a data transformation on the source excile file to nvarchar for the column. This way it imports the numnerical values and the word NULL as string. I will be adding another step in the package to change the string "NULL"'s to actual NULL value.

The problem, as noted in the above comments, is that the excel source column with Values of NULL is being interpreted as a string "NULL" as opposed to the actual NULL value. I'm hopping someone else will have a cleaner solution.

Answer 3

Doing a ctrl+f and replace the string "NULL" with nothing in the spreadsheet before importing worked great for me