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.