Forum Discussion

pleblond's avatar
pleblond
New Contributor
5 years ago

Extra characters inserted in DB when using Bulk Loader

We have a pipeline with a SQL bulk upload snap in it.
Here is our input:

ID: 1
JSON : {"address: 1234 no where”}

We insert several hundred of these into a SQL DB into a table with
ID,JSON

Create SQL:
CREATE TABLE [dbo].[test_json_inc](
[id] [bigint] NOT NULL,
[json] nvarchar NULL
)
GO

When we do a SQL insert the following is added ot the json field
JSON : {"address: 1234 no where”}

If we switch to using the bulk uploader we get
JSON : {"address: 1234 no where”} [|!,!|]1[|!,!|]{"address: 1234 no where”}

As you can see the json is duplicate with the pipes and exclamations in between. Is this a known issue and is their a workaround?

pipeline

3 Replies

  • mbowen's avatar
    mbowen
    Employee
    5 years ago

    Hi Paul:
    This may be a bug in our Bulk Load snap. Let me confirm the behavior and see if can provide a workaround if that’s the case. Expect another response from me.

    • mbowen's avatar
      mbowen
      Employee
      5 years ago

      Ok, this is not obviously broken for me. I need a bit more information from you. The Bulk Load snap uses the bcp utility to load data into SQL Server.

      The data fed into the Bulk Load snap is written to a temp file using custom delimiters. Specifically these:

      FIELD delimiter: [|!,!|]
ROW delimiter: [|!/n!|]

      We invoke bcp specifying the temp data file plus the custom delimiters. The data file would of course explain what we’re seeing in the db, though this is a bit tricky to inspect because it is deleted after the load snap finishes.

      I see a space after the first json block before another field delimiter, which should be a record delimiter, and then repeat of data. Hmmm…

      What version of SQL Server are using? The version of BCP would be helpful too (bcp -v). Are you running a Windows or Linux plex? Sample input would be helpful – maybe you can download the data that is fed into the Bulk Load snap.

      We can actually replay the data and invoke BCP from the command line. It’s good you checked that could load data using SQL Insert – a nice cross check. Something with the shape of the data going into bulk it seems.

      • pleblond's avatar
        pleblond
        New Contributor
        5 years ago

        Sadly we switched to using a straight SQL insert so I don’t have any more examples. Also due to privacy I am limited to what I can send you.

        We are on SQL version: 2019,

        We are using BCP version: - That is on the snaplogic side so we aren’t sure.

        And it is a windows machine. Windows 2019 Data Center

        I wish I could offer more help but its definitely bulk uploader or the bulk uploader snap. During validation the data looks fine and swapping out the control for a sql insert works fine.

        Thanks