Add additional column (json elements) to pipeline json dynamically

Question

Hi Team,

I am working on a ADF pipeline where using a Copy Data activity where source is D365(Dataverse) and destination is Azure Sql DB.
We have a external configuration table which is getting used to read all the parameters for example, "Column Mapping", " Source Query" etc. and replacing it in pipeline JSON (Below screenshot) , which is working fine.

I am using expression syntax like below to achieve that.

"query": {
"value": "@{item().Fetchxml}",
"type": "Expression"
}
When I am trying to do the same for "Additional Column" , the expression syntax giving an error, as for other cases its a first level json object which can contain other json objects, array etc. But for Additional Column part, we need to place all the collection of JSON objects inside the array, which is erroring out.

Our standard JSON sample that I am trying to place inside array (Within "additionalColumns": [ <here> ]) is like below:

{
"name": "sfa_substatusentitytype",
"value": {
"value": "sfa_substatus",
"type": "Expression"
}
}

Please let me know how we can place it dynamically , just like the one I am placing for Fetcxml (Mentioned above). TIA.

Answer 1

Hi @CHAKRABARTI, Suryasish ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your ask, you are trying to copy data from D365(Dataverse) to Azure Sql DB. During this process, you are trying to add additional column and also, trying to perform upsert while doing the data load. Please correct me if my understanding is having some gap.

The issue is not in the additional column json, the first screenshot which you shared having the below code looks correct:

{  
"name": "sfa_substatusentitytype",  
"value": {  
"value": "sfa_substatus",  
"type": "Expression"  
}  
}  

However, the issue is with making upsert keycolumn dynamic for which you are using this syntax:

"keys":[  
"@{item().upsertkey}"  
]  

That is incorrect. Kindly try the below syntax:

"keys": {  
                                            "value": "@array(item().upsertkey)",  
                                            "type": "Expression"  
                                        }  
                                      

Or, you can also try:

"keys": {  
                    "value": "@split(item().upsertkey,',')",  
                    "type": "Expression"  
                }  

For more details, kindly check the following 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 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