Upsert in dynamic pipeline

Shubhangi Sanjay 1 Reputation point

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": []

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,103 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
7,172 questions
{count} votes

2 answers

Sort by: Most helpful
  1. AnnuKumari-MSFT 23,271 Reputation points Microsoft Employee

    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 130616-image.png or upvote 130671-image.png 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

  2. Shubhangi Sanjay 1 Reputation point


    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 , ?