SQL Server Mapping JSON column not found

I have a pipeline in ADF which has suddenly failed due to being unable to find a column: 'Prop_1'
ErrorCode=MappingColumnNameNotFound,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column name 'Prop_1' is not found in the source table. Please check the column in 'mappings'.,Source=Microsoft.DataTransfer.ClientLibrary,'

This is an Excel to SQL copy task and the Excel doc I am working with has no column headers hence passing Prop_X in my mapping JSON (extract below):

{
    "type": "TabularTranslator",
    "typeConversion": true,
    "typeConversionSettings": {
        "culture": "en-gb"
    },
    "mappings": [
        {
            "source": {
                "name": "Prop_0"
            },
            "sink": {
                "name": "EmployeeID"
            }
        },
        {
            "source": {
                "name": "Prop_1"
            },
            "sink": {
                "name": "Forename"
            }
        }
    ]
}

I have manually checked the file via the UI and when trying to import the mapping, ADF returns the ordinal values rather than Prop_X as previously. ADF showing ordinal mapping

When I click preview data however, Prop_X is clearly visible: Preview Source showing Prop columns

There is already an unanswered post on this from ~3 months ago ( Previous Post ). I can't see anything else on Microsoft and the documentation suggests what I have set up is correct.

Any suggestions?

As per the Documentation,
For the delimited text file and excel files without headers, the column are considered as ordinals instead of names in the mapping.

If the file don't have any header, then the mapping should be with ordinals, else it will give the above error. Even if you don't import the schema in mapping, it will give the same error.

To avoid the error, your mapping needs to be with the ordinals that means you need to import the schema in copy activity for this kind of files.

If you want to set the schema dynamically in copy activity mapping instead of manual schema importing, follow the below steps.

Get the structure array from the target SQL table using Get Meta data activity.

Start the dynamic mapping like below with string {"type": "TabularTranslator","mappings": .

Now, use a Foreach activity to build the mapping array. Give this expression in the Foreach activity and check the Sequential.

@range(0,activity('Get Metadata1').output.columnCount)

Inside the ForEach, use append variable activity to an empty and give the below expression.

@json(concat('{"source": {"ordinal":"',string(add(item(),1)),'","type": "String"},"sink": {"name": "',activity('Get Metadata1').output.structure[item()].name,'","type": "',activity('Get Metadata1').output.structure[item()].logicalType,'"}}'))

After ForEach, take another set variable activity and concat the total mapping with below expression.

@concat(variables('schema'),string(variables('mapping')),',"typeConversion": true,"typeConversionSettings":{"allowDataTruncation": false,"treatBooleanAsNumber": false}}')

This will generate the schema JSON like below for sample.

Give this variable final_schema in the copy activity mapping dynamic content.

@json(variables('final_schema'))

Now, debug the pipeline and your excel data will be copied to the SQL table.

Result:

My pipeline JSON:

{
    "name": "pipeline2",
    "properties": {
        "activities": [
            {
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [
                    {
                        "activity": "final_schema",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "ExcelSource",
                        "storeSettings": {
                            "type": "AzureBlobFSReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        }
                    },
                    "sink": {
                        "type": "AzureSqlSink",
                        "writeBehavior": "insert",
                        "sqlWriterUseTableLock": false
                    },
                    "enableStaging": false,
                    "translator": {
                        "value": "@json(variables('final_schema'))",
                        "type": "Expression"
                    }
                },
                "inputs": [
                    {
                        "referenceName": "Excel1",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "AzureSqlTable1",
                        "type": "DatasetReference"
                    }
                ]
            },
            {
                "name": "Get Metadata1",
                "type": "GetMetadata",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "dataset": {
                        "referenceName": "AzureSqlTable1",
                        "type": "DatasetReference"
                    },
                    "fieldList": [
                        "structure",
                        "columnCount"
                    ]
                }
            },
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Start schema",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@range(0,activity('Get Metadata1').output.columnCount)",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Append variable1",
                            "type": "AppendVariable",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "mapping",
                                "value": {
                                    "value": "@json(concat('{\"source\": {\"ordinal\":\"',string(add(item(),1)),'\",\"type\": \"String\"},\"sink\": {\"name\": \"',activity('Get Metadata1').output.structure[item()].name,'\",\"type\": \"',activity('Get Metadata1').output.structure[item()].logicalType,'\"}}'))",
                                    "type": "Expression"
                                }
                            }
                        }
                    ]
                }
            },
            {
                "name": "Start schema",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Get Metadata1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "schema",
                    "value": "{\"type\": \"TabularTranslator\",\"mappings\":"
                }
            },
            {
                "name": "final_schema",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "ForEach1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "final_schema",
                    "value": {
                        "value": "@concat(variables('schema'),string(variables('mapping')),',\"typeConversion\": true,\"typeConversionSettings\":{\"allowDataTruncation\": false,\"treatBooleanAsNumber\": false}}')",
                        "type": "Expression"
                    }
                }
            }
        ],
        "variables": {
            "schema": {
                "type": "String"
            },
            "mapping": {
                "type": "Array"
            },
            "final_schema": {
                "type": "String"
            }
        },
        "annotations": []
    }
}

SQL Server Mapping JSON column not found

1条答案

相关问题

热门标签

最新问答