This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Right Truncation on column

Go to solution
fmdf
New Contributor III

‎04-19-2021 07:02 AM

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?
0 Kudos
1 ACCEPTED SOLUTION

Go to solution
fmdf
New Contributor III

‎04-20-2021 08:08 AM

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.

View solution in original post

0 Kudos
3 REPLIES 3

Go to solution
mbowen
Employee
Employee

‎04-19-2021 03:06 PM

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 …

0 Kudos

Go to solution
fmdf
New Contributor III

‎04-20-2021 08:08 AM

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.

0 Kudos

Go to solution
mbowen
Employee
Employee
In response to fmdf

‎04-20-2021 08:58 AM

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
0 Kudos