How to resolve SSIS Package Truncation error

0

SSIS Error: 0xC02020A1
Tying to import data to SQL 2008 from CSV file, I am getting below error.

> Error: 0xC02020A1 at Data Flow Task, Source – Distribution by xyz
> table from CSV [1]: Data conversion failed.
> The data conversion for column "ID" returned status value 4 and
> status text "Text was truncated or one or more characters had no match
> in the target code page.".    

previous have used varchar and never a problem, I have tried to convert data to Int and even increased the size but still getting this error. I have also tried using the Advance editor and changed data to almost anything I could think would cover datatype on that column, still getting an error. Thanks for the advice

sql
tsql
sql-server-2008
ssis
asked on Stack Overflow Oct 18, 2018 by SQLfun

1 Answer

0

Most likely you have "bad" records in your raw file.

For "bad", if could be one of these two: 1) implicitly conversion cannot be done to the string value; 2) string is too large (exceed 8000).

For debugging this, change the destination column to VARCHAR(MAX).

Then load the raw file (do not forget to increase the external column length to 8000 in the Advanced page in flag file connection manager).

if:

1) it is loaded successfully, query the table where ISNUMERIC([that numeric column]) =0, if anything returned, that is the bad record that cannot be converted when loading.

2) it is not loaded correctly, try to see if you have any value from that field has more than 8000 characters. (could use C# script if it is impossible to manually check)

answered on Stack Overflow Oct 18, 2018 by LONG

User contributions licensed under CC BY-SA 3.0