Questions on using the SQL Server – Merge snap, where is WHEN MATCHED logic?

[bubba]

Hello,

I’m a new user to Snaplogic, so go easy. 🙂 My question is on using the “SQL Server – Merge” snap:
I’ve created a pipeline to import from a sftp csv-formatted source to merge (update or insert) into a table on an SQL server database.

It’s partially working! When I run the pipeline it successfully goes through the sftp->CSV parser->Mapper->SQL Server – Merge snaps to insert the csv-mapped records into the MS-SQL target db table with no problem.

But when running a second time I get a “Violation of PRIMARY KEY constrain” error, so new records can be inserted, but not updated.

My questions are:

1. When looking at the Merge snap settings I see no place to put the WHEN MATCHED or WHEN NOT MATCHED BY logic?

2. How is the Identity Column setting used? I though it might be a setting for the primary unique key, but that does not seem to be the case.

3. With other pipelines I was able to setup a route-error-data view to capture errors into a file. When I attempt that with the SQL Server Merge snap I get an ‘Error branch will overlap with existing Snaps” error. I’ve attempted to rearrange the pipeline snaps but to no avail, I still cannot setup the error route view.

I’ve also reviewed and read the snap documentation at:
https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1439070/SQL+Server+-+Merge

I see that there’s a “ELT Merge Into” snap, but I do not have access to that snap from the designer tool.
https://docs-snaplogic.atlassian.net/wiki/spaces/SD/pages/1123745847/ELT+Merge+Into
Should I be using that snap instead?

Thanks for any insights or guidance!

[Matthias]

1. your screenshot suggests that you forgot to tick the “evaluate as expression” - so the content (ID = $['xxxx']) will be evaluated as string - ignoring the reference to the input document.
2. Identity column is usually the ID column - the one that’s autogenerated, and in most cases i’ve encountered with SQL Server, a UUID within the database.
3. you should look at what is placed next (on top of) the sql server merge snap. This error usually appears if there’s no room for the (new) output or input view on the canvas. You can unsnap it from all other elements and move it to a free space in your canvas, which should allow you to add an error view (you’ll then obviously have to find a room where it’ll fit in).

[bubba]

Much thanks for your feedback. I’m still testing but it’s now working as expected as far as I can see.