You may be eligible for a free six-month trial of Dynamics 365 Marketing! Read this blog to learn how it works.
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.
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.
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!