โ05-10-2021 12:47 PM
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?
โ05-10-2021 01:00 PM
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.
โ05-10-2021 03:08 PM
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.
โ05-13-2021 06:45 AM
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