topic Re: Data Truncation error message does not tell which column raised it in Talend Studio

Data Truncation error message does not tell which column raised it

Anonymous — Wed, 05 Sep 2012 14:25:42 GMT

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.

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

Anonymous — Wed, 05 Sep 2012 14:58:38 GMT

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.

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

alevy — Mon, 10 Sep 2012 03:09:52 GMT

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

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

Anonymous — Mon, 10 Sep 2012 14:47:31 GMT

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

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

alevy — Tue, 11 Sep 2012 02:19:56 GMT

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.