Can we make the ADF JSON mapping in COPY Activity Case-insensitive?

Question

So, actually I'm using the ADF Copy Activity to load the CSV files from the azure blob storage to snowflake table. And column mapping is done using the json with source and sink column name as below:

{ "type": "TabularTranslator",
            "mappings": [
                {
                    "source": {
                        "name": "first_name",
                        "type": "String"
                    },
                    "sink": {
                        "name": "FIRST_NAME",
                        "type": "String"
                    }
                },
                {
                    "source": {
                        "name": "Address",
                        "type": "String"
                    },
                    "sink": {
                        "name": "ADDRESS",
                        "type": "String"
                    }
                }

my csv file will come with the two column first_name and Address but the problem here is that I can receive the column name in any case like FIRST_NamE or address.

So is there any way to make this column mapping case-insensitive?

Answer 1

You can build the mapping dynamically and then with the help of toUpper() function, you can convert the column name to uppercase.

The mapping format would be same as given in the question which I have built dynamically using:

• Get Metadata activity to get the header as single value as shown in the below image:

• Set variable activity to build the required map.

• For each to create an array of each column map.

• The last set variable activity to create the final mapping.

• Finally, copy data activity to with desired mapping as dynamic content.

The result would be as shown below:

The following are the pipeline JSON for:

Pipeline JSON:

{
    "name": "pipeline3",
    "properties": {
        "activities": [
            {
                "name": "get comma seperated header",
                "type": "Lookup",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "DelimitedTextSource",
                        "storeSettings": {
                            "type": "AzureBlobFSReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "DelimitedTextReadSettings"
                        }
                    },
                    "dataset": {
                        "referenceName": "csv1",
                        "type": "DatasetReference"
                    }
                }
            },
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "mapping",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@split(activity('get comma seperated header').output.firstRow['Prop_0'],',')",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "map",
                            "type": "AppendVariable",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "maps",
                                "value": {
                                    "value": "{\"source\": {\"name\": \"@{item()}\",\"type\": \"String\"},\"sink\": {\"name\": \"@{toUpper(item())}\",\"type\": \"String\"}}",
                                    "type": "Expression"
                                }
                            }
                        }
                    ]
                }
            },
            {
                "name": "mapping",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "get comma seperated header",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "map",
                    "value": {
                        "value": "{\"type\": \"TabularTranslator\",",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "final",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "ForEach1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "final",
                    "value": {
                        "value": "@concat(variables('map'),'\"mappings\": [',join(variables('maps'),','),']}')",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [
                    {
                        "activity": "final",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "DelimitedTextSource",
                        "storeSettings": {
                            "type": "AzureBlobFSReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "DelimitedTextReadSettings"
                        }
                    },
                    "sink": {
                        "type": "DelimitedTextSink",
                        "storeSettings": {
                            "type": "AzureBlobFSWriteSettings"
                        },
                        "formatSettings": {
                            "type": "DelimitedTextWriteSettings",
                            "quoteAllText": true,
                            "fileExtension": ".txt"
                        }
                    },
                    "enableStaging": false,
                    "translator": {
                        "value": "@json(variables('final'))",
                        "type": "Expression"
                    }
                },
                "inputs": [
                    {
                        "referenceName": "src",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "DelimitedText1",
                        "type": "DatasetReference"
                    }
                ]
            }
        ],
        "variables": {
            "map": {
                "type": "String"
            },
            "maps": {
                "type": "Array"
            },
            "final": {
                "type": "String"
            }
        },
        "annotations": []
    }
}

• Get Metadata source dataset JSON:

{
    "name": "csv1",
    "properties": {
        "linkedServiceName": {
            "referenceName": "adls",
            "type": "LinkedServiceReference"
        },
        "annotations": [],
        "type": "DelimitedText",
        "typeProperties": {
            "location": {
                "type": "AzureBlobFSLocation",
                "fileName": "input.csv",
                "fileSystem": "data"
            },
            "columnDelimiter": "|",
            "escapeChar": "\\",
            "firstRowAsHeader": false,
            "quoteChar": "\""
        },
        "schema": [
            {
                "name": "FiRsT_NaME",
                "type": "String"
            },
            {
                "name": "Address",
                "type": "String"
            }
        ]
    }
}

NOTE: I have used csv as sink for demonstration.