Returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page

Question

I downloaded the flat file from the FDA official site. The file is NDC Database File - Text Version (Zip Format).

I unzipped it and got product.txt.

I tried to import it into my database using SSIS.

All columns were varchar(max).

SSIS failed with the error message:

[Flat File Source 2] Error:

Data conversion failed. The data conversion for column "PHARM_CLASSES" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

I have no solution and need help please.

Answer 1

I was simulated your process, so the problem is that for some reason the "Flat File Connection Manager" recognize the "columns width" as 50 for all the columns(the actual size is more than that), and you have more than one "problematic column" like (LABELERNAME, SUBSTANCENAME, etc.)

So for each such column change the "columns width" to 3000 and it will work for you. If you want to be more specific you can open the file on excel and find the MAX LEN per column and then change the "columns width" respectively.

Answer 2

varchar max can hold around 8000 characters, so you can go with increasing output column width. You also need to be extra conscious about field tyes very specific dates and try to pass it NULL in case it is not available in source data.