Forum Discussion

bubba's avatar
bubba
New Contributor
10 months ago

Truncation error with MS SQL Bulk Load snap, column reported not issue, which column is real issue?

 

We're occasionally getting a frustrating truncation error using MS SQL Bulk Load snap. The error 'column' reported is -not- the issue, so which column is causing issue??

Error details below. I can isolate the incoming record from the incoming csv file (Row 74740 in this case) , the record/doc has many fields and the reported column (Column 13 in this case) is -NOT- where the data truncation error is happening. 

Is there a way I can pin point the actually field/column which the trunc error is happening?

I suspect the fields are being re-ordered in some manner to feed to the bcp command for the snap. Is there a way to see the -actual- bcp cmd text the snap is passing to bcp for execution?

The 'Column 13' reported in this error is not helpful in debugging the pipeline when it fails with these data truncation errors.

MUCH THANKS for any assistance or insights!

Error below:

Failed loading record

Resolution:
Please address the reported issue.

Reason:

#@ Row 74740, Column 13: String data, right truncation @#

w

 

 

  • dwhansen-cbg's avatar
    dwhansen-cbg
    Contributor
    6 years ago

    I’ll reply to my own post. This error was due to timeout between SnapLogic cloudplex and on our AWS PostgreSQL instance. Adding Sort snaps in front of all my insert and update SQL snaps resolved the issue for me. I don’t quite fully understand the reasoning, but works.

    • alchemiz's avatar
      alchemiz
      Contributor III
      6 years ago

      Hi Davis,

      I didn’t that issue but I was limited to send script in execute to only 50… something index out of bounds…

      The SQL script that I was generating is upsert using the ON CONFLICT DO UPDATE… but the table have so… so… many columns, I was wondering were you able to do a bulk load using csv so that no need to declare the columns… or do you have a template that will set the columns during runtime… what is the format of the schema table ?? is it like declaring a variable in SQL? I’m kinda newbie with postgres

      I have this topic opened any suggestion/insights?

      Thanks,
      EmEm

      • dwhansen-cbg's avatar
        dwhansen-cbg
        Contributor
        6 years ago

        I haven’t used the bulk load feature at all. When I stage my data I just use a mapper snap and then an insert. I suppose you could dynamically set all the column names, but it isn’t something I’ve actually done in SnapLogic.