datafactory copy activity with dynamic mapping

Question

Hello,

I am trying to modify my JSON pipeline file to use a translator instead of static mapping ...

When I statically define the values like:

translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "name": "variable1",
                                    "type": "Decimal"
                                },
                                "sink": {
                                    "name": "v1",
                                    "type": "Decimal"
                                }
                            },
                            {
                                "source": {
                                    "name": "variable2",
                                    "type": "Guid"
                                },
                                "sink": {
                                    "name": "v2",
                                    "type": "Guid"
                                }
                            },
}

I can read my parquet file with exactly 2 columns.

Unfortunately, I cant find the way to do it dynamically. If I do something like this, I return all the source columns when reading the dataframe and the column names dont change. Basically, the dynamically defined mapping is ignored by ADF.

"enableStaging": false,
"translator": {
    "value": "@variables('final_mapping')",
    "type": "Expression"
}

"variables": {

            "final_mapping": {
                "type": "Array",
                "defaultValue": [
                    {
                        "source": {
                            "name": "variable1",
                            "type": "Decimal"
                        },
                        "sink": {
                            "name": "v1",
                            "type": "Decimal"
                        }
                    },
                    {
                        "source": {
                            "name": "variable2",
                            "type": "Guid"
                        },
                        "sink": {
                            "name": "v2",
                            "type": "Guid"
                        }
                    }
                ]
            }
        },

Can somebody help me? thanks in advance.

Answer 1

Hi Jorge García ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding , you were facing issue while trying to perform dynamic mapping in ADF.

Glad that you figured out the way to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

According to Microsoft Docs (https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#default-mapping) it is necessary to create the finalMapping variable as a JSON

{"type":"TabularTranslator","mappings":[{"source":{"name":"Id"},"sink":{"name":"CustomerID"}},{"source":{"name":"Name"},"sink":{"name":"LastName"}},{"source":{"name":"LastModifiedDate"},"sink":{"name":"ModifiedDate"}}]}

So after storing the mappings array in a variable, creating a JSON file like above resolved the issue.

More details: Dynamic Column mapping in Copy Activity in Azure Data Factory

Kindly accept the answer by clicking on Accept answer button so that the solution reaches the community in case anyone is facing similar issue. Thankyou.

Answer 2

Finally I managed to find the solution:

According to Microsoft Docs (https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#default-mapping) it is necessary to create the finalMapping variable as a JSON like the following:

{"type":"TabularTranslator","mappings":[{"source":{"name":"Id"},"sink":{"name":"CustomerID"}},{"source":{"name":"Name"},"sink":{"name":"LastName"}},{"source":{"name":"LastModifiedDate"},"sink":{"name":"ModifiedDate"}}]}

So after storing my mappings array in a variable, I also had to create a JSON file like the one I pasted.

Kind regards.

Answer 3

You need to use a Set Variable activity before the Copy Data activity to assign a value to the final_mapping variable dynamically.

For the Copy Data activity, it should references th final_mapping variable correctly as an expression. The syntax for referencing variables dynamically in ADF requires the use of @{}.

And then update the translator section in the Copy Data activity to reference the variable directly.

Your json should look like below :

"activities": [

    {

        "name": "SetFinalMapping",

        "type": "SetVariable",

        "typeProperties": {

            "variableName": "final_mapping",

            "value": {

                "type": "Array",

                "value": [

                    {

                        "source": {

                            "name": "variable1",

                            "type": "Decimal"

                        },

                        "sink": {

                            "name": "v1",

                            "type": "Decimal"

                        }

                    },

                    {

                        "source": {

                            "name": "variable2",

                            "type": "Guid"

                        },

                        "sink": {

                            "name": "v2",

                            "type": "Guid"

                        }

                    }

                ]

            }

        }

    },

    {

        "name": "CopyDataWithDynamicMapping",

        "type": "Copy",

        "typeProperties": {

            "source": {

                "type": "ParquetSource"

            },

            "sink": {

                "type": "YourSinkType"

            },

            "enableStaging": false,

            "translator": {

                "type": "TabularTranslator",

                "mappings": {

                    "value": "@variables('final_mapping')",

                    "type": "Expression"

                }

            }

        }

    }

]