Looking up GUID in Azure Data Factory from CRM

Question

I am working on a SQL to CRM copy data mapping. I want to either update an account record if it exists or insert a new one.

In CRM the accound_id GUID field is the primary key. I want to be able to look that up in CRM if it exists or generate a new one.

I think I am already generating one correctly:

SELECT
CAST(NULL as uniqueidentifier) AS ACCOUNTID FROM TABLE;

How do I proceed to see if the record exists, then update, otherwise generate a new guid and insert.

Answer 1

Hi @Shamsuddin, Natasha ,

Welcome to Microsoft Q&A platform and thanks using this forum.

Firstly sorry to tell that I do not have a CRM instance to generate a lab and provide step by step implementation, but I will try my best to present all the info required for your requirement.

As per my understanding, you would like to generate a guid in ADF Copy activity and then do an upsert in CRM (accound_id GUID field is the primary key).
If that is the requirement, then you can use Additional column feature in ADF copy activity to generate a guild value column as shown below.

As Dynamics CRM supports Upsert, you can set the write behavior of your sink settings as Upsert and Ignore Null values as True as shown below. You can leave the alternate Key as empty if primary key is used.

And then map the AdditionalGuidColumn to the destination upsert column i.e., accound_id GUID field which is the primary key.

For additional info about Dynamics sink settings, please refer to this doc: Dynamics as a sink type

Please let me know how it goes. In case if I misunderstood your requirement, please correct me with a bit clarification, we will be happy to assist further.

Looking forward to your confirmation.

Thank you

----------

Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.