Data Truncation error message does not tell which column raised it

One Star

Data Truncation error message does not tell which column raised it

I have a delimited text file with about 20 columns and I have to load the data into a table in MSSQL. I configured a reject flow out of the MSSQL Output component into another text file to manage the records that have data issues. When i run this job it outputs a few reject rows and the error message from the reject flow just says "Data Truncation - Line 34256". It does not tell me which column in line 34256 raised this error. Is there a way too get more information about the data truncation? I tried to find similar issues on the forum but did not get an answer.
Community Manager

Re: Data Truncation error message does not tell which column raised it

Hi
The error message shows the length of inserted data exceeds the max length of field defined in table. To debug the error, check the 'die on error' option on tmssqloutput, the job will throw the error and die.
----------------------------------------------------------
Talend | Data Agility for Modern Business
Seven Stars

Re: Data Truncation error message does not tell which column raised it

It's not possible. Even SQL Server Management Studio does not tell you which column is the problem when you insert data.
One Star

Re: Data Truncation error message does not tell which column raised it

Hi,
To debug the issue you can do following.
1) Create a similer table as your output table, using some syntex like create table new_table as select * from old_table where 1=0
2) Now point the mapping to populate this table.
3) In t<DBOut> Advanced settings set Commit every as 1
4) Run the job and after failure check the table's last row.
5) Find that row in your source most probably the culprit is the next row to it. Otherwise you can check one row before, this row and next row.
6) Hopefully you will get the erronous row.
Alternatively you can check the column lengths also and filter them in some flat file. Check the column lengths as defined in the database.
Once you find that column modify it to accomodate future requirements and remove filter if required from performance perspective.
Hope that helps.
--
Regards,
Vinod
Seven Stars

Re: Data Truncation error message does not tell which column raised it

You can also add a tSchemaComplianceCheck before tMSSqlOutput with a reject flow. That will tell you which column is the problem but you need to ensure the schema is identical to the table design.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads