Upsert in dynamic pipeline

Question

I am copying multiple tables with combination of keys using copy activity. My pipeline was working fine until there was only one primary key.

As a solution I tried creating a new column to my mapping table that contains 'null' if there is no secondary key, else it contains second key.

However, it throws following error:

{
"errorCode": "2200",
"message": "ErrorCode=SqlUpsertKeyColumnsInvalid,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Sql upsert key column '[NULL]' does not exist in the table '[##InterimTable_9c714e57-ad0c-48a7-ab65-3c8b845b275f]'.,Source=Microsoft.DataTransfer.ClientLibrary,'",
"failureType": "UserError",
"target": "Copy data1",
"details": []
}

Answer 1

Hi @Shubhangi Sanjay ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your issue , you are trying to perform Upsert in Azure Synapse analytics , however, in your case the key column is a composite key , that means, combination of two columns. You want to know the way to handle it in the pipeline. Please correct me if my understanding is wrong.

You can store the key columnnames in the same field in lookup table . For example: KeyColumn: C2_id,Date_added . In the copy activity, inside keycolumns option, add dynamic content and provide the following expression : @split(item().keyColumn,',')

This will convert the keycolumns as ["C2_id","Date_added"]

For more details, kindly watch out this video: How to perform Upsert for Incremental records using Azure Synapse Pipelines

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

@AnnuKumari-MSFT

Yes I completely understand and I have stored the key values of dbo.C2 as "C2_id, Date_added" but where to put this? I am retrieving the value from @activity('Lookup1')output.firstRow.keyColumn

My pipeline:

If I try to directly use @split(item().keyColumn,',') in keyColumn I get the following error:

{
"errorCode": "InvalidTemplate",
"message": "The expression 'split(item().keyColumn,',')' cannot be evaluated because property 'keyColumn' doesn't exist, available properties are 'name, type'.",
"failureType": "UserError",
"target": "Copy data1",
"details": ""
}

So the real question is how to split @activity('Lookup1')output.firstRow.keyColumn using , ?