Forum Discussion

fmdf's avatar
fmdf
New Contributor III
5 years ago
Solved

Right Truncation on column

Without any changes to target or source, I’m now getting this error:

Failed loading record, reason=#@ Row 2346, Column 9: String data, right truncation @#

Questions:

  1. Column 9 is not the actual name of a column. How am I supposed to figure out what column 9 is?
  2. How do I actually see the data for Row 2346 so I can try to determine what the error is?
  • fmdf's avatar
    fmdf
    5 years ago

    I found the issue today.

    We had initially been using AWS Data Migration Services for the table in question. It auto created a column of Numeric(38,10). DMS did this for ALL Oracle columns with a data type of NUMBER. We did analysis on the source data (which is just Oracle NUMBER with no precision) and didn’t see any examples of non-integer data. We converted that column to a numeric(18,0).

    Job ran for days with no issue and then suddenly failed…column 9, row x was truncating.

    I opened up SSMS and did a generate create table script. Column 9 in the list was Numeric (18,0) as mentioned.

    I queried the source table and found 1 row with decimal data. I modified my target column and now the job works.

    I wish it was easier and we got better error messaging. It would have made things quicker.

3 Replies

  • mbowen's avatar
    mbowen
    Employee
    5 years ago

    Hi David:

    Can you give me a few more specifics. This seems like an error that could be raised by the SQL Server Bulk Load snap. If so, a “String data, right truncation” often indicates that we are writing too much data into a field (ie, exceeding max length of a field). In this case, what’s changed is the data.

    For Bulk Load, we write data to a temp file and then pass this to BCP to load into the server. We may be able to determine field by inspecting columns of input document (ie, 9th column). Anyway, I won’t say much more because you may respond that you are referring to something else …

  • fmdf's avatar
    fmdf
    New Contributor III
    5 years ago

    I found the issue today.

    We had initially been using AWS Data Migration Services for the table in question. It auto created a column of Numeric(38,10). DMS did this for ALL Oracle columns with a data type of NUMBER. We did analysis on the source data (which is just Oracle NUMBER with no precision) and didn’t see any examples of non-integer data. We converted that column to a numeric(18,0).

    Job ran for days with no issue and then suddenly failed…column 9, row x was truncating.

    I opened up SSMS and did a generate create table script. Column 9 in the list was Numeric (18,0) as mentioned.

    I queried the source table and found 1 row with decimal data. I modified my target column and now the job works.

    I wish it was easier and we got better error messaging. It would have made things quicker.

    • mbowen's avatar
      mbowen
      Employee
      5 years ago

      We’ve added a debug view to some of our snaps, for example, the SOAP snap, and plan to do similar for more snaps. Hopefully, we’ll be able to make hunting down these kinds of problems easier in the future. Good job on identifying and fixing this.

      • M