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.
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.
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
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.
