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

Question

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.

Any help would be appreciated.

Thanks

Answer 1

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.

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

Answer 2

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.