Dynamic Mapping of JSON Sink Data in ADF

Question

Hello Microsoft Community,

I hope you're all doing well. I'm currently working on a project in Azure Data Factory and need some advice!
We are successfully able to preview source data from the Salesforce Linked Service and sink data using the REST Linked Service for Business Central in Azure Data Factory (ADF). However, we are facing an issue with schema import since the sink data is in JSON format.

Could anyone suggest an approach for dynamically mapping the source data to the sink using the 'Add Dynamic Content' feature in ADF?

Thanks in advance!

Vinit.

Answer 1

Use a Copy Activity to transfer data from Salesforce (or any source) to Business Central via REST API.

While configuring the Sink side, you'll have to map the fields dynamically using dynamic expressions under the Mapping tab.

In the Sink section of the Copy Activity, click on Mappings. This is where you can map source fields to the sink (JSON format in your case).

Instead of hardcoding the mappings, select the Add Dynamic Content feature to create flexible mappings based on runtime variables or parameters.

Define parameters in your pipeline for source column names and target JSON field names.

Use these parameters inside your mappings by referring to them in the Add Dynamic Content window.

Example:

@pipeline().parameters.targetField

Since your sink is in JSON format, you can use Data Flow instead of direct copy to handle more complex transformations and schema alignment.

Within the Data Flow, you can use the Derived Column transformation to restructure or rename the columns as per the target's JSON structure.

You can also use the Flatten transformation if your source contains hierarchical data that needs to be transformed into a flat JSON structure for Business Central.

Make sure your REST linked service in ADF is correctly configured for POST or PUT methods, depending on the API requirements of Business Central.

If you need to pass the JSON dynamically, you can use Web Activity in combination with a Copy Activity, or you can configure your REST API dataset to accept dynamic content (via expressions) for both headers and body.