May 24, 2014

I am importing an excel spread sheet into a sql server database and is getting the error "DTS_E_INDUCEDTRANSFORMFAILUREONERROR" and it more messages like "The value could not be converted because of a potential loss of data" and "The source column is of nVarchar(272 -length) and the destination column is nVarchar(256 -length)"

So, really there are 2 failure messages here. After some investigation, I realised that with the first message "The value could not be converted because of a potential loss of data", it's because one of the rows had a double value whereas I initially set the sql table to have int data type. As a result, it failed while converting this particular row. Changing the data type to float fixed this issue.

The second error message is because the string value in the excel is lot bigger in length compared to the max length set in the sql table. Changing the max number of characters in sql table fixed this issue.

Overall, importing an excel spread sheet to sql table is quite easy provided correct data types are applied to the sql table.

0 comments:

Reference: Shahed Kazi at AspNetify.com