ADF upsert error locating key column in the interim table, HELP

Question

I have a very complicated error, my Schema parameters and keys come from the DB.

My upsert keys come separated by comma and double quotation mark. And in this part I send it as an array.

ErrorCode=SqlUpsertKeyColumnsInvalid,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Sql upsert key column '["PERIOD_SET_NAME","PERIOD_NAME","DB_ID"]' does not exist in the table '[bi_dsa].[InterimTable_dc71bc97-9fa8-4271-ba78-46ded5377b78]'.,Source=Microsoft.DataTransfer.ClientLibrary,'

Answer 1

Hi @Ricardo Alejandro Guerrero García ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

After looking at the Key Column I don't think it is an issue with double quotes or comma separator. Could you please confirm what is the Key Column that has to go into the configuration? Are you having single Key column for upsert or multiple Key columns? If multiple Key Columns then seems like something is wrong with the @item().key_upsert value.

I just tried a similar upsert with multiple Key columns and passed them as an array and it worked as expected without issue.

Here is a similar configuration I had:

I have loaded the array for Key columns to a set variable to see how the value looks like. In my case if was as below.

Could you please store @item().key_upsert value to a variable and compare with my sample and see what's the difference? Please do share image of the value so that we can compare and assist accordingly.

My assumption is that the value you are passing to the Key Column dynamically is causing the problem. Also would recommend passing the hard coded values to see if that works as expected. If it works then it confirms that the value you are passing from @item.key_upsert is the one creating the problem.

Hope this info helps. Do let us know how it goes.