1

Example of offending row

Row 1 "This is a line of data"
       "User entered another line of data in the same row"
Row 2

I'm unable to find a solution to this error on google: "Text was truncated or one or more characters had no match in the target code page."

This happens only on one field which has comments. The offending Excel row/column has text that was entered in two lines i.e they entered the data and pressed "enter" and wrote a new line in the same row.

Im using an Excel file source in SSIS and an OLEDB Destination (SQL Server) but one column keeps erroring out and I have tried to do the following:

1) Change output column width in advanced editor (still errors)

2) Data conversion tool between the source and destination (still errors)

Any help would be appreciated, thank you.

Joscion
  • 49
  • 6
  • Is it an XLS or a CSV? – Mike D. Jun 04 '15 at 08:28
  • Make sure that you are loading into a non production table and change the error output of that column to ignore truncation. This will push the data into the table and you can see what the end result is in your database. Hopefully this will give you an idea if it is a line feed tag or a true truncation. The other thing to try is making sure that your read width is set wide enough in you file connection manager. Doesn't matter how wide your output is if your read width is too short. – SFrejofsky Jun 04 '15 at 12:34

2 Answers2

1

I solved the issue by moving the row to the top of the excel file and created a new connection.

To avoid having to move the row up in the file, the TypeGuessRows Registry value should be 0.

The Registry value for TypeGuessRows is default set to 8. Which means Excel checks only 8 rows to decide the maximum length of column.

If we have maxim length in other row instead of first 8 rows , then it will get an error.

To overcome this issue we need to set TypeGuessRows=0 in the registry ,which reads all the columns of Excel and decides maximum column length of column.

Thanks

Joscion
  • 49
  • 6
0

Text was truncated or one or more characters had no match in the target code page When importing from Excel file

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5cbdea26-3951-4fd8-b53b-3db6a17b088e/-text-was-truncated-or-one-or-more-characters-had-no-match-in-the-target-code-page?forum=sqlintegrationservices

these lniks will help

Community
  • 1
  • 1
Rahul Sharma
  • 453
  • 3
  • 10