Dynamic Mapping in ADF Copy Activity Not Retrieving OData Formatted Values

Question

Setup:

• Source: API dataset (OData API link).
• Additional Headers: Prefer = odata.include-annotations="*" (to retrieve lookup values).
• **API Response Example:
  
• Manual Mapping (Works Fine)

1. Imported schema.
2. Set Collection reference as $['value'].
3. Mapped source columns to destination columns manually using the dropdown.
4. Data loads correctly, including statecode@OData.Community.Display.V1.FormattedValue

• Dynamic Mapping (Issue)
  1. To avoid manual mapping for 200+ tables, I created a JSON-based dynamic mapping and stored it in a variable (testjsonmapping):
  2. Added dynamic mapping in Mapping Table using: @json(variables('testjsonmapping'))
  3. json mapping code as below,(for testing purpose i have selected only one column as having formatted value).
  
• Issue:
• Pipeline runs successfully.
  • However, statecode@OData.Community.Display.V1.FormattedValue returns NULL instead of the expected value "Active".
  • Other non-annotated fields like "statecode" load correctly.
• Question:
• How can I correctly retrieve the formatted value annotations (e.g., statecode@OData.Community.Display.V1.FormattedValue**)** dynamically in ADF Copy Activity? Is there any alternative approach for achieving dynamic mapping for multiple tables without manually configuring mappings for each one?

Would appreciate any insights or workarounds!

Answer 1

When you manually map the columns, ADF correctly retrieves the formatted values but

when you use dynamic mapping, the formatted values return NULL, even though non-annotated fields are correctly populated.

The possible causes of your problem :

• The path in your dynamic mapping might not correctly reference the annotated fields.
• The JSON structure used for dynamic mapping might not align with the expected format for retrieving annotated values.

The path in your JSON mapping should correctly reference the annotated fields :

{

    "source": {

        "path": "statecode@OData.Community.Display.V1.FormattedValue"

    },

    "sink": {

        "name": "statecodename",

        "type": "String"

    }

}

You can use a Flatten transformation to handle nested structures and annotations. or a custom activity (using Azure Functions) to preprocess the data and extract the required annotations before loading it into the destination.

Answer 2

Hello Akshay Patel,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that your dynamic mapping in ADF Copy Activity is not retrieving OData Formatted Values.

To really resolve this issue, this is my best recommendation:

• Use Data Flows to extract and map OData annotations instead of Copy Activity.
• Inspect the API response using a Web Activity before applying dynamic mapping.
• If Copy Activity must be used, first store API results in a staging area, then process annotations separately.
• Test the API response manually before running the pipeline to verify that formatted values are included.

With any of the two methods below, you should be able to retrieve the formatted value annotations (e.g., statecode@OData.Community.Display.V1.FormattedValue) dynamically in ADF Copy Activity.

METHOD 1: THIS IS TO USE DATA FLOW

1. Use a Copy Activity to load raw API response (including annotations) into a staging table (Azure SQL, Blob Storage, or ADLS).
2. Use a Data Flow to transform the data:

Use Derived Column transformation to extract statecode@OData.Community.Display.V1.FormattedValue.

Rename extracted fields before writing them to the destination. This is an example of Data Flow Derived Column Expression:

iif(contains(statecode, '@OData.Community.Display.V1.FormattedValue'), statecode['@OData.Community.Display.V1.FormattedValue'], 'Unknown')

3. Load the transformed data into the final destination.

METHOD 2: THIS IS TO USE WEB ACTIVITY + COPY ACTIVITY

1. Call the API with Prefer = odata.include-annotations="*" to inspect how the formatted values are returned. Then, parse the response in a Set Variable activity in web activity.
2. Modify the JSON Mapping to Reference Extracted Annotations, instead of statecode@OData.Community.Display.V1.FormattedValue, create a derived field in the API response and map it dynamically. The below is a refine JSON mapping for dynamic retrieval of OData formatted values in ADF Copy Activity from your JSON, to ensure that:

• The Prefer header includes odata.include-annotations="*"`
• The dynamic mapping correctly references the @OData.Community.Display.V1.FormattedValue annotation .

{
  "type": "TabularTranslator",
  "mappings": [
    {
      "source": {
        "name": "statecode@OData.Community.Display.V1.FormattedValue",
        "type": "String"
      },
      "sink": {
        "name": "statecode_formatted",
        "type": "String"
      }
    },
    {
      "source": {
        "name": "statecode",
        "type": "Int32"
      },
      "sink": {
        "name": "statecode",
        "type": "Int32"
      }
    }
  ]
}

NOTE: Ensure that the API response contains the annotation as a top-level field before applying dynamic mapping.

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.