ADF动态列映射加载数据至Synapse专用SQL池时遇列数不匹配错误

问题场景 #

有多份记录布局不同的源文件，尝试将数据加载至Synapse Analytics专用SQL池的同一张表，使用动态列映射方法时触发报错。操作流程：设置变量 → 执行Copy Activity。

环境信息 #

源文件（dept.csv）内容 #

File : dept.csv 
SourceID,SourceName
1,HR
2,Finance
3,IT
4,Marketing

目标表（dept）创建语句 #

create table dept
(
    dept_id INT,
    dept_name VARCHAR(100),
    location VARCHAR(100)
);

Copy Activity报错信息 #

"errors": [
    {
        "Code": 22301,
        "Message": "Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Column count in target table does not match column count specified in input. If BCP command, ensure format file column count matches destination table. If SSIS data import, check column mappings are consistent with target.,Source=.Net SqlClient Data Provider,SqlErrorNumber=107098,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=107098,State=1,Message=Column count in target table does not match column count specified in input. If BCP command, ensure format file column count matches destination table. If SSIS data import, check column mappings are consistent with target.,}

已配置的动态映射变量内容 #

{
    "type": "TabularTranslator",
    "mappings": [
        {
            "source": {
                "name": "SourceID",
                "type": "int",
                "physicalType": "String"
            },
            "sink": {
                "name": "dept_id",
                "type": "Int32",
                "physicalType": "int"
            }
        },
        {
            "source": {
                "name": "SourceName",
                "type": "String",
                "physicalType": "String"
            },
            "sink": {
                "name": "dept_name",
                "type": "String",
                "physicalType": "varchar"
            }
        }
    ]
}

问题原因 #

报错明确指向目标表列数与输入列数不匹配：目标表dept包含3列，但当前动态映射仅定义了2列的映射关系，Copy Activity默认要求输入列数与目标表列数完全对应，或显式处理未映射列。

解决方案 #

针对未映射的location列，可采用两种处理方式：

方式1：添加默认值映射 #

修改动态映射变量，为location列添加固定默认值（如空字符串或NULL），确保映射列数与目标表一致：

{
    "type": "TabularTranslator",
    "mappings": [
        {
            "source": {
                "name": "SourceID",
                "type": "int",
                "physicalType": "String"
            },
            "sink": {
                "name": "dept_id",
                "type": "Int32",
                "physicalType": "int"
            }
        },
        {
            "source": {
                "name": "SourceName",
                "type": "String",
                "physicalType": "String"
            },
            "sink": {
                "name": "dept_name",
                "type": "String",
                "physicalType": "varchar"
            }
        },
        {
            "source": {
                "value": ""  // 也可指定为NULL："value": null
            },
            "sink": {
                "name": "location",
                "type": "String",
                "physicalType": "varchar"
            }
        }
    ]
}

方式2：启用Sink的"允许插入到SELECT"选项 #

在Copy Activity的Sink配置中启用Allow insert into select，允许只插入已映射的列，忽略未映射列：

• 进入Copy Activity的Sink配置页面
• 找到Table option下的Allow insert into select并勾选启用
• 保存配置后重新运行Copy Activity

补充说明 #

后续加载其他布局的源文件时，只需根据源文件列结构调整动态映射：

• 确保必要列的映射关系正确
• 源文件中不存在的目标表列，要么添加默认值映射，要么通过Sink配置允许忽略未映射列

内容的提问来源于stack exchange，提问作者user2358844