Azure Synapse Copy Mapping adds (NULL)

I am trying to do manual mapping in Synapse Copy Activity. My Source is Json files from ADLS2 & Sink is SQLDB Table.

One of the column in Json is Emp. Name which need to mapped to Emp_Name so as per this document I tried following dynamic contents

@json('{
    "translator": {
        "type": "TabularTranslator",
        "typeConversion": true,
        "mappings": [
            {
                "source": {
                    "name": "Emp. Name"
                },
                "sink": {
                    "name": "Emp_Name"
                }
            }
        ],
        "collectionReference": "",
        "mapComplexValuesToString": true
    }
}')

and

@json('{
    "translator": {
        "type": "TabularTranslator",
        "mappings": [
            {
                "source": {
                    "path": "$[''ID'']"
                },
                "sink": {
                    "name": "ID"
                }
            },
            {
                "source": {
                    "path": "$[''Emp. Name'']"
                },
                "sink": {
                    "name": "Emp_Name",
                    "type": "String"
                }
            },
            {
                "source": {
                    "path": "$[''ChannelFormat'']"
                },
                "sink": {
                    "name": "ChannelFormat"
                }
            },
            {
                "source": {
                    "path": "$[''ChannelClassification'']"
                },
                "sink": {
                    "name": "ChannelClassification"
                }
            }
        ],
        "collectionReference": "",
        "mapComplexValuesToString": true
    }
}')

But this adds (NULL) to the Emp_Name column in the table.

I also tried static mapping for all columns present in Json data file to table columns But I got

"ErrorCode=UserErrorSchemaMappingCannotInferSinkColumnType,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Data type of column 'ChannelFormat' can't be inferred from 1st row of data, please specify its data type in mappings of copy activity or structure of DataSet.,Source=Microsoft.DataTransfer.Common,'"

Source JSON file is list of json in it.

[  {"ID":"12345",.....,"Emp. Name":"ABC",...."ChannelFormat":null,"ChannelClassification":null},
   {"ID":"67890",.....,"Emp. Name":"XYZ",...."ChannelFormat":null,"ChannelClassification":null} ]

Emp_Name column is of data type NVARCHAR(100) in SQL DB table

I tried to import schema using Import Schema option but Interactive authoring is disabled, so I cant use this

What I am missing here ?

"ErrorCode=UserErrorSchemaMappingCannotInferSinkColumnType,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Data type of column 'ChannelFormat' can't be inferred from 1st row of data, please specify its data type in mappings of copy activity or structure of DataSet.,Source=Microsoft.DataTransfer.Common,'"

The reason for the above error might be that there is no data type specified in the mapping for the columns.

follow the below steps to achieve your requirement.

This is my sample source json:

[
    {
        "ID":12345,
        "Emp. Name":"ABC",
        "ChannelFormat":null,
        "ChannelClassification":null
    },
    {
        "ID":67890,
        "Emp. Name":"XYZ",
        "ChannelFormat":null,
        "ChannelClassification":null
    }
]

Give the source and sink datasets in the copy activity and give the below mapping.

Click on import schemas and specify the target columns.

Now, your data will be copied without any errors.

Update:

You can build your mapping JSON like this and give it in the mapping dynamic content expression.

@json('{"type": "TabularTranslator","mappings": [{"source": {"path": "$[''ID'']"
                                },
                                "sink": {
                                    "name": "Id",
                                    "type": "Int32"
                                }
                            },
                            {
                                "source": {
                                    "path": "$[''Emp. Name'']"
                                },
                                "sink": {
                                    "name": "Emp_name",
                                    "type": "String"
                                }
                            },
                            {
                                "source": {
                                    "path": "$[''ChannelFormat'']"
                                },
                                "sink": {
                                    "name": "ChannelFormat",
                                    "type": "String"
                                }
                            },
                            {
                                "source": {
                                    "path": "$[''ChannelClassification'']"
                                },
                                "sink": {
                                    "name": "ChannelClassification",
                                    "type": "String"
                                }
                            }
                        ],
                        "collectionReference": ""
                    }')

This mapping JSON built for the above sample JSON only. If you have more columns than this, you need to add more objects of source and sink keys to the mappings array.