PowerObjects Blog 

for Microsoft Business Applications


Azure Data Factory (ADF) V2 – Lookup

Post Author: Rahul Pingale |

ADF is a data integration service based in the cloud and is a part of Microsoft’s analytics suite. However, ADF Pipeline is used for an Extract Transform Load purpose (Data Integration/Data Migration between two systems – on-prem or cloud – on a bigger scale).

A Pipeline is a data-driven workflow where data is orchestrated to transform as per Target system requirements. ADF Pipeline is both powerful and can be complex at the same time. A Pipeline deals with Data Transformation/Massaging, and most of the times we must work with lookup fields (Entity Reference GUID fields) without having a GUID into source data itself. Let’s see how to handle this scenario.

Your source system has a column for Account Manager name / email value of the user with string datatype or an Account Type field with string data type. And in the Target Dynamics 365 system, your entity has these fields as Lookup and OptionSet types, so you can not directly use source data as in target sink.

This is where we must park the staging data from target system prior to going into data transformation mode for source data.

1. Select OOB CRM entity or use FetchXML to retrieve staging data and park it for further data transformation process.

2. Stage CRM System Users data where we can have AccountManager name/email values and SystemUser’s GUID and stage data from CRM OOB entity StringMap which holds data for Entityname, Controlname, textvalue and int value for an Optionset fields in system.

3. Now go to the Data Flow activity.

  • Source 1 = Select Source dataset (AccountManager name/email and AccountType optionset face display text value)
  • Source 2 = Select staging dataset (Systerusers entity data with name/email and User record GUID)
  • Source 3 = Select staging source dataset (StringMap data for Optionsets fields)

4. Apply a join on Source 1 AccountManager name/email with Source 2 Staging name/email field. Choose appropriate join methodology (Full Outer, Inner, Left Outer, Right Outer and Cross join) that’s suits your business requirement. As a result, in join we would get User GUID for each source matching record.

5. Now, apply join on Join output and Source 3 to match Optionset text values and to get int value in output.

6. Sink this transformed data and use GUID and Optionset Int values to populate CRM lookups and Optionset fields.

Note: Regular lookups in CRM system works very straight and can only be associated to a specified entity, we can use field schema like in Mappings tab while loading data to Target system. But there are other lookups that can be associated to either Users or Teams entities. With a small syntax change we can modify field schema logically to make it ready to use while mapping fields to load data in Target system.

  • Lookup field name = accountmanager (lookup on Account entity for a User record)
  • Either by using derived column you can add a new field accountmanager_type
  • Or you can use Additional Columns feature on Copy Activity to add new field with default value as, accountmanager@EntityReference

Now, while mapping the fields to Target system all you need to do is select default accountmanager field value from source / transformed dataset and map it to the Target entity field. And select newly created field accountmanager_type field and set a defaulted value as accountmanager@EntityReference.

Hopefully this helps. Happy D365’ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

Leave a Reply

Your email address will not be published. Required fields are marked *

PowerObjects Recommends