Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
It sounds like it's treating the NULL in excel as the string 'NULL'... –  JNK Apr 3 '13 at 16:35
    
@jnk That seems to be the exact problem. I was able to resolve my issue it was a dirty/hack type of solution. I will post as answer until maybe someone else posts a better cleaner solution. –  JuanVelez Apr 3 '13 at 16:39

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.