Upsert in dynamic pipeline

Shubhangi Sanjay 1 Reputation point
2022-10-31T05:30:58.02+00:00

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.
255420-screenshot-3.png

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.
4,365 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,535 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AnnuKumari-MSFT 30,676 Reputation points Microsoft Employee
    2022-11-01T08:20:16.137+00:00

    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
    2022-11-04T08:10:51.107+00:00

    @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

    257077-image.png

    My pipeline:
    257183-image.png

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