How to upsert records from Azure Synapse dedicated SQL Pool table Dataverse table in Azure Data Factory?

Anirudh Mounasamy 101 Reputation points
2023-12-26T12:11:16.4066667+00:00

I want to move data regularly from Synapse SQL pool to dynamics 365 dataverse, Everytime I do that I need to truncate the table in dataverse and run the pipeline in ADF, instead of truncating everytime I found we can do upsert to the dataverse table.

Table in Synapse SQL Pool: Recommendation_All_Progress Table in Dataverse: cr726_progressreporttest Column set as Alternate Key in Dataverse: 'cr726_contactid'

I need to update records based on the above mentioned column.

If the data for that column is in source and in the dataverse, it needs to updated in dataverse, else it needs to inserted as a new row.

I couldn't figure out how can I do this in ADF data flow. Can someone give me the correct method to do this?

If the above provided details is insufficient, please let me know.

I have tried it in data flow, but got this error: User's image

can anyone clarify the error?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,186 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,246 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Burak Kaan Ozalp 0 Reputation points
    2024-07-11T13:30:37.42+00:00
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.