Azure Data Factory: collectionReference not supported in dynamic TabularTranslator

Question

Hi there,

The Copy Activity doesn't use the specified "collectionReference" value in the dynamic mapping.

Consider the payload:

{
  "skip": 0,
  "take": 2,
  "rows": [
    {
      "Column_one": 42,
      "Column_two": 42
    },
    {
      "Column_one": 33,
      "Column_two": 33
    }

  ]
}

When I use the interface the code generated is:

"translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "path": "['Column_one']"
                                },
                                "sink": {
                                    "name": "Column_one",
                                    "type": "Int32"
                                }
                            },
                            {
                                "source": {
                                    "path": "['Column_two']"
                                },
                                "sink": {
                                    "name": "Column_two",
                                    "type": "Int32"
                                }
                            }
                        ],
                        "collectionReference": "$['rows']"
                    }

Then the resulting table in Azure SQL Server would be, more or les:
Column_one | Column_two
42 | 42
33 | 33

But when I provide the exact same code via "Add dynamic content" on the mapping page of the Copy Activity, it completely ignores the specified "collectionReference": "$['rows']", inserting exactly one row with only NULL values.

Can ADF please support the collectionReference parameter provided via the dynamic content on Mapping?

Thanks in advance.

Answer 1

@TerrenceV In order to make dynamic content work you need to cast the dynamic mapping content to JSON in the dynamic section. I have replicated your ADF pipeline and instead of adding the code directly to the dynamic editor I have created a variable and stored the translator code including collectionreference in the variable (@mapping). Later I have used the variable in dynamic mapping by casting to json using @json () function. Please refer to the below animation for the same.

I have tested the ADF flow by moving the data from cosmos db to SQL table with dynamic mapping and it works as expected and I see two records in SQL table.

Please try it out and let me know if you have any issues.

----------

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.

Answer 2

Oh wow, thanks for your elaborate answer Saurabh Sharma!

I assumed that the response of my Lookup was a json, casting to json explicitly did the trick. I have a lookup which I used in this fashion:

@activity('Get Mapping').output.firstRow.json_output

Changing that to:

@json (activity('Get Mapping').output.firstRow.json_output)

Made it work like a charm. Thanks!

Answer 3

The collectionReference seems to work but stops working when an unmapped json field is encountered from the source? The rest of the fields on the collectionReference become null valued.