How do I dynamically map columns in Data Factory to Dynamics 365?

Question

Hi,

I currently have a pipeline in Data Factory that will use a view from an Azure db to insert data into an entity in Dynamics 365. While this is working as expected, the next part of the process needs to update Dynamics 365. The number of fields that I need to be able to update in Dynamics 365 are over 45 and not all will need to be updated all the time. Basically I need to somehow dynamically map which field Dynamics needs to update from the results of a query.

I can do the checks in the Azure db and produce the results as follows

id fieldtoupdate value

where the id will be the identifier for the record, however I am pretty sure this will not work as I'm expecting, because it's basically not in a tabular format.

Any tips or ideas are much appreciated.

Edited to add that all the fields that need to be updated sit in the same entity.

Answer 1

@Diana Rus

Thanks for the question and using MS Q&A platform.

To dynamically map columns in Azure Data Factory (ADF) to Dynamics 365 (Microsoft Dataverse), you can follow these steps:

Copy Data Activity with Dynamic Mapping:

• In your ADF pipeline, use a Copy Data activity to move data from your source (Azure DB) to Dynamics 365.
  • Within the Copy Data activity, navigate to the Mapping tab.
  • Add dynamic content to the mapping properties. The syntax should look like this:

         @json(activity('Lookup_name').output.firstRow.column_name)
    

  • You can also pass dynamic mapping JSON as a parameter from a pipeline parameter file.

Data Flow Transformation (for more complex scenarios):

• If you need more advanced transformations, consider using an ADF Data Flow.
• Add a Select transformation to the source output.
• In the Select settings, use the Rule-based mapping option to write dynamic expressions that match column names with the sink columns in Dynamics 365.

please check this link:https://stackoverflow.com/questions/69768398/how-can-i-make-data-flow-column-mapping-dynamic-in-azure-data-factory-based-on-d

Hope this helps. Do let us know if you any further queries.