ADF Copy Dynamic Upsert Column Key

Question

ADF Copy Dynamic Upsert Column Key

Duncan Bode 2

Hi,

I am experiencing an error attempting an Upsert using the Copy activity in Data Factory.

In the Keys column I have used dynamic content to assign a variable to the Keys Column field with the primary key required for mapping the source to the target tables however it fails when debugging the pipeline with the following error..

Error:

ErrorCode=UserErrorUnexpectedObjectTypeInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of property 'keys' is in unexpected type 'List`1'.,Source=Microsoft.DataTransfer.ClientLibrary,'

I don't understand what 'List 1' is refering to in the error message, it is definitally passing in the correct id as I can see this in the JSON produced from the run, any ideas?

Thanks in advance!

Duncan

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-11T07:49:56.437+00:00

Hi @Anonymous ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-12T13:43:45.443+00:00

Hi @Anonymous ,
Just following up to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

4 answers

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-11T07:49:56.437+00:00

Hi @Anonymous ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-12T13:43:45.443+00:00

Hi @Anonymous ,
Just following up to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi @Anonymous ,

Welcome to Microsoft Q&A platform and thankyou for posting your query.

As I understand your question here, you are trying to perform upsert on Azure SQL tables using Copy activity. In the sink settings, when you are trying to pass the key columns creating Variable it is failing .

We are expected to pass the column list in the form of an Array. When we directly hardcode the column list in the KeyColumns , ADF creates it as the items of an array. Therefore, either directly supply the hardcoded key column values in the sink settings or create a variable with data type as array and use it as the Key Column to perform the upsert.

In order to achieve the requirement of passing the Key Column via variable , we need to make the column as an Array data type as shown in the below screenshot.

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

Akhil 21 Reputation points

2022-04-05T14:05:21.327+00:00

Hi @AnnuKumari-MSFT ,

thanks for your reply , I have followed the steps you mentioned above , but in my case I will be pulling the key colum details from the data base , so in this process the value is getting converted to string by the time it reaches to the variable.So , how can I convert this string to Array.

Below is my error.

The variable 'kcolumns' of type 'Array' cannot be initialized or updated with value of type 'String'. The variable 'kcolumns' only supports values of types 'Array'.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-05T14:49:27.777+00:00

Hi @Akhil ,

Thanks for the ask. Could you please try making use of @array function to achieve this requirement. Attaching video for your reference.

If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
Akhil 21 Reputation points

2022-04-06T08:09:46.89+00:00

Hi @AnnuKumari-MSFT ,

thanks for your reply. I understood the concept here but in case if I have multiple key columns in a single table like user,id. How can I insert these two column names into key columns dynamically which are coming from database column as above.

Thanks
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-06T09:52:56.313+00:00

Hi @Akhil ,

Thanks for the ask. In that case you need to pass multiple columns as the items of an array . To do that I would suggest to use @createArray Function and pass all the columns dynamically. Here is the video for help:

If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-07T10:09:14.23+00:00

Hi @Akhil ,
We haven’t heard from you on the last response and was just checking back to see if the issue got resolved . If the suggested response helped you, please click Accept Answer and kindly upvote the same. If you have extra questions about this answer, please do Comment.
Akhil 21 Reputation points

2022-04-07T11:32:54.357+00:00

I cannot see the button accept answer
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-07T11:39:53.39+00:00

Hi @Akhil ,

This thread has been created by a profilename : @Anonymous
Being the original poster only @Anonymous has the rights to accept the answer. If that is your profile, please feel free to sign in with that profile and try accepting answer.

To know more on how to accept answer , please check : How to accept an answer in Q&A
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-04-18T07:05:32.783+00:00

Hi @Anonymous ,
Just checking in to see if the above answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
Mohammed Safi Ahmed 1 Reputation point

2022-10-28T06:58:38.34+00:00

Did you try storing key values as comma separated values in a single column in the database? How did you work around this?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-11-02T06:03:30.53+00:00

Hi @Mohammed Safi Ahmed ,
Kindly watch out : How to perform Upsert for Incremental records using Azure Synapse Pipelines

Answer 2

Céline Rodríguez 16

HI! Have you tried the function array()?
i.e. @array(concat(item().SourceTableName,'BK'))
I create a composite BK in all my table and then use it in my upsert.

HOSSAIN, Muhammad 0 Reputation points

2023-01-23T04:00:44.6633333+00:00

This is the one exactly worked in my case;

Thanks Celine

Answer 3

Andy Wilbourn 11

I used a column to enter the column names separated by a comma and used the split(<column>, ',') that will result in returning an array. It works for me perfect.

Sujatha Pillai 11 Reputation points

2022-12-13T05:03:17.86+00:00

@Andy Wilbourn ,
Could you please give us some example how to use split function in ADF?

I am trying to get key column dynamically like below.

select Keycolumn
from stagingarea.parameters;

Keycolumn has Id,CId

@array(pipeline().parameters.Keycolumn)

key column '[Id, CId]' does not exist in the table

I don't know how to add single quote for each column ie ['Id', 'CId']

Answer 4

Sujatha Pillai 11

Hi All,

The below functions array & split together is working for me

@array(split(pipeline().parameters.Keycolumn,','))

Also found in the below video
https://www.youtube.com/watch?v=UGfJ3s9YGjs&t=1521s

Somesh Bhangale (IN) 0 Reputation points

2023-08-28T14:26:30.0366667+00:00

Its not working for me, could you plz suggest another solution. It taking as it is input@array(split(pipeline().parameters.Keycolumn,',')) in Key

Share via

ADF Copy Dynamic Upsert Column Key

4 answers

Your answer