Using default mapping for JSON to SQL Server Table Copy Data

Question

Is it possible to use Default (rather than Explicit) mapping in a Copy Data activity? I can only get it working with explicit mapping but want to make the pipeline generic. For example, my return JSON from the source looks like:

{
"results": [
{
"$tip": "9a239370b8f9696b31d299a9c801547afebccebf8d4e8ecb1aafa6c3852c74ae",
"logCreatedDateAndTime": "2021-04-30T20:42:28+0000",
"roleName": "DAT Manager/Supervisor"
},
{
"$tip": "844f9d9ebd02e4ac0fa1f20402e86e4d8940d8788ecd603ddca499f07816b138",
"logCreatedDateAndTime": "2021-04-30T20:42:28+0000",
"roleName": "DAT Manager/Supervisor"
}
]
}

The SQL table has three nvarchar(max) columns with identical names ($tip, logCreatedDateAndTime, roleName).

If I do Explicit mapping, I get records pulled into the SQL table. If I leave the mapping cleared. I get a single record with all nulls. I've tried setting the collection (i.e. setting Collection Reference to $['results']) without specifying the fields and that didn't work. When I do the Explicit mapping, the fields are mapped on the mapping tab without requiring any manual corrections so it seems source and sink fields are properly aligned.

Answer 1

Hi @Heckman, Jim ,

Welcome to Microsoft Q&A forum and thanks for reaching out.

No, the default mapping won't work for hierarchical to tabular data when there is a collection reference. This is because Data Factory cannot infer the desired collection reference. And when you specify the collection reference then it is no longer a default mapping as it expects the fields to be mapped. If you don't map the fields it will treat as empty record.

An alternate we could think of is by passing explicit mapping as a parameter which is nothing but parameterized mapping. This approach is suitable if you know the source & sink schema upfront.

For more info please refer to this document and see if that helps to achieve your requirement: Parameterize mapping in ADF Copy activity

Additional info: Hierarchical source to tabular sink in ADF Copy activity

Hope this info helps.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.