"Text was truncated or one or more characters had no match in the target code page.".

Question

"Text was truncated or one or more characters had no match in the target code page.".

VoldemarG

Hall of Fame

Points: 3883
More actions
February 19, 2019 at 9:05 am

#373844

This error somehow for some reason makes me so deeply sad that i cannot even think where i should start fixing it.
spent HOURS, no success...
Does anyone encounter it and is there a one fix to it?
There are 400 columns in the csv file being imported. i cannot possibly be fixing column by column...
[Flat File Source [236]] Error: Data conversion failed. The data conversion for column "City" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Likes to play Chess

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

palandri Old Hand Points: 387 More actions · Answer 1

You might have to fix this type of error on a column by column basis. I haven’t been working with SSIS very long, so there is likely a better way, but what I do is set the input column properties in the flat file connection manager. When the flat file connection manager is created, it seems to set input columns to DT_STR width 50 by default. One or both of these attributes may be incorrect for your situation.

They can be changed on the Advanced page of the connection manager. If truncation is occurring, it may be because the width of the input column in the connector is larger than that of the destination column in the database.

Try setting that column’s width in the connection manager to the same size or smaller as the destination column.

Thom A SSC Guru Points: 99129 More actions · Answer 2

Considering this is SSIS, I would suggest directing your failed rows to a different output (i.e. a text file) and then inspect those. We don't know your data or the definition of it, as it's not in your post, but you do. Likely you'll spot the city which has a longer than expected name in there and can adjust your data, or definition of your table appropriately.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

VoldemarG Hall of Fame Points: 3883 More actions · Answer 3

palandri - Tuesday, February 19, 2019 9:31 AM
You might have to fix this type of error on a column by column basis. I havenâ€™t been working with SSIS very long, so there is likely a better way, but what I do is set the input column properties in the flat file connection manager. When the flat file connection manager is created, it seems to set input columns to DT_STR width 50 by default. One or both of these attributes may be incorrect for your situation.
They can be changed on the Advanced page of the connection manager. If truncation is occurring, it may be because the width of the input column in the connector is larger than that of the destination column in the database.
Try setting that columnâ€™s width in the connection manager to the same size or smaller as the destination column.

Thanks! Is "directing your failed rows to a different output" considered a complex task? How long would i need to kearn how to do it?

Likes to play Chess

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

when i press ADD button to add error output it says cannot create it.
Is there a way to let package continue writing to sql table , SKIPPING (or ouputting elsewhere) the rows that cause error?

Likes to play Chess

palandri Old Hand Points: 387 More actions · Answer 5

You didn’t say where you are pressing the Add button, but in general, the way to redirect error output from an OLE DB destination is to open the editor and select the Error Output page. Set the value in the Error column to Redirect row, it’s probably Fail component by default.

Once that’s done, connect the error output (red connector) to where you want the error rows to go, perhaps a flat file destination.

@Taps SSC Eights! Points: 879 More actions · Answer 6

Another way to debug this can be importing the data from csv file into sql server database manually using data import task in SSMS - then you can query it in different ways to figure out which rows are causing issue (eg using MAX(LEN(city))

Thanks