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.
Option3:
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.
Option3:
No comments:
Post a Comment