ADF Azure SQL sink - Key column 'columnName' does not exist in mapped columns.

Marc 31 Reputation points
2022-10-18T15:21:12.697+00:00

Hello everyone, newbie here with ADF :-)

I'm trying to build a simple dataflow with ADFv2 that reads data from an Azure Data Lake gen 2 storage and sync it to an Azure SQL database table. (data in the Lake comes from Dataverse and synched with Azure Synapse Link)

If I do a simple "source to destination" flow everything works fine, but when I activate the "Allow upsert" update method and set the Key column in the sink settings, I get the following error message when validating: "Key column 'SectorCode' does not exist in mapped columns." However, the column exists in the Azure SQL schema and is present in the mapping list. Am I doing something wrong? I've searched for a solution on the web but could not find anything.

251673-image.png

251530-image.png

Any help would be appreciated.

Thanks

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,891 questions
{count} vote

Accepted answer
  1. ShaktiSingh-MSFT 14,186 Reputation points Microsoft Employee
    2022-10-19T12:16:33.91+00:00

    Hi @MarcArsenault-7348,

    Welcome to Microsoft Q&A platform and thanks for using Azure Services.

    This issue is transient and occurs sometimes. It’s a bug on our UX tool, it complains the key column as “undefined” because the schema of sink is not published so the UX tool can’t recognize it properly. We will check on the work item for it and fix this issue soon.

    The workaround steps and video to follow are below:

    1). Go to the Sink tab Dataset and Deselect the Azure SQL DB dataset and select any other dataset from the dropdown.
    2). Again, re-select the Azure SQL DB dataset.
    3). Next go to the Settings tab and click Validate.
    4). The Error will no longer exist, and the Data flow will be validated.

    252024-alterrowsolution.gif

    Hope this will help. Sorry for the inconvenience.
    Please try and let us know in case of 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
    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Zoran Liu 6 Reputation points Microsoft Employee
    2022-10-24T05:28:29.75+00:00

    Hello @MarcArsenault-7348

    We have already had a fix for this. Could you please try it again? If this issue still exists, please provide us with some repro steps. And thank you for reporting this.

    1 person found this answer helpful.
    0 comments No comments