17.9.08

excel column is greater than 255 in ssis

We used to face some problems like, while migrating data from excel to sql server error occurs due to lengthy data.

SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.”

Reason for this error:
1. The error is due to the lengthy data in the excel.
2. SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

Considering am having a column named "Name"
First 8 rows is of length <255
9th row is of length > 255.
While executing the package you will get the above error, its because your input value will be truncated and SSIS wont allow for that. In this case,
Two types of logics can be followed,

Option1 : Its tricky one, just copy the 9th row to the top (may be as 1 row).Now try to create your package using import/export wizard. Your ssis will create column with width nvarchar(Max) which will accept upto 2 GB.

Option2: Change the Preparation SQL task query, change the data type as varchar(Max) and modify the excel source ->advanced editor and change the output columns type and length and external column type and length.

Happy Learning!!!
Regards,
Venkatesan Prabu .J
at

6 comments:

  1. Murali.MOctober 23, 2008 at 3:02 AM

    Very useful Venkat.I had face the problem one year before.Now i got the solution..Thank u for your solution..i hope it will lot of people.

    ReplyDelete
  2. AnonymousFebruary 27, 2009 at 10:37 AM

    Could you elaborate on your second solution? what do you mean by Preparation SQL task query? I am also facing this issue for both imports and exports

    ReplyDelete
  3. FulgencioNovember 25, 2010 at 2:03 AM

    It works very well. Thanks a lot for your comment, it was very usefull.

    Thanks again!

    ReplyDelete
  4. AnonymousJune 27, 2011 at 9:57 AM

    Thank you, this was very helpful. My import to SQL Server from an Excel spreadhsheet worked perfectly with this solution.

    ReplyDelete
  5. AnonymousJune 21, 2013 at 8:27 AM

    Thanks for this..
    This was my problem. First 8 rows had text which was <255 characters
    2. SSIS has an inbuilt logic to scan the data in the spread sheet. It will scan the first 8 rows and based on that it will written a logic to build the table's logic for the package. If your lengthy data in not in the 8 row then your ssis wont respond it.

    ReplyDelete
  6. AnonymousJanuary 29, 2014 at 10:37 AM

    Thanks Venkat for your help - This is amazing

    --Saurabh

    ReplyDelete