Using ADF Copy Activity with dynamic schema mapping

Question

I'm trying to drive the columnMapping property from a database configuration table. My first activity in the pipeline pulls in the rows from the config table. My copy activity source is a Json file in Azure blob storage and my sink is an Azure SQL database.

In copy activity I'm setting the mapping using the dynamic content window. The code looks like this:

"translator": {
    "value": "@json(activity('Lookup1').output.value[0].ColumnMapping)",
     "type": "Expression"
 }

My question is, what should the value of activity('Lookup1').output.value[0].ColumnMapping look like?

I've tried several different json formats but the copy activity always seems to ignore it.

For example, I've tried:

{
    "type": "TabularTranslator",
    "columnMappings": {
      "view.url": "url"
    }
}

and:

"columnMappings": {
    "view.url": "url"
}

and:

{
  "view.url": "url"
}

In this example, view.url is the name of the column in the JSON source, and url is the name of the column in my destination table in Azure SQL database.

Fang Liu · Accepted Answer · 2018-07-03 02:23:14Z

2

The issue is due to the dot (.) sign in your column name.

To use column mapping, you should also specify structure in your source and sink dataset.
For your source dataset, you need specify your format correctly. And since your column name has dot, you need specify the json path as following.
You could use ADF UI to setup a copy for a single file first to get the related format, structure and column mapping format. Then change it to lookup.

And as my understanding, your first format should be the right format. If it is already in json format, then you may not need use "json" function in your expression.

edited Jul 3, 2018 at 2:23

answered Jul 3, 2018 at 2:16

Fang Liu

2,3612 gold badges15 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

typheon Over a year ago

Thank you! JSONPath expression was indeed where my issue was. Unfortunately I now realize that the JSONPath expressions do not support the "dynamic content" option, so I do not think I can drive that from my configuration database :(

Fang Liu Over a year ago

For JSON path not support dynamic content, I think that could be an UI issue. You could have a try with json code directly.

Don Scott Over a year ago

@FangLiu It is not a UI issue. The product has a bug where the expression language is ignored for schema mapping column names.

VladDrak · Accepted Answer · 2019-09-04 19:24:54Z

2

There seems to be a disconnect between the question and the answer, so I'll hopefully provide a more straightforward answer.

When setting this up, you should have a source dataset with dynamic mapping. The sink doesn't require one, as we're going to specify it in the mapping.

Within the copy activity, format the dynamic json like the following:

    {
      "structure": [
        {
          "name": "Address Number"
        },
        {
          "name": "Payment ID"
        },
        {
          "name": "Document Number"
        },
          ...
          ...
      ]
    }

You would then specify your dynamic mapping like this:

    {
      "translator": {
        "type": "TabularTranslator",
        "mappings": [
          {
            "source": {
              "name": "Address Number",
              "type": "Int32"
            },
            "sink": {
              "name": "address_number"
            }
          },
          {
            "source": {
              "name": "Payment ID",
              "type": "Int64"
            },
            "sink": {
              "name": "payment_id"
            }
          },
          {
            "source": {
              "name": "Document Number",
              "type": "Int32"
            },
            "sink": {
              "name": "document_number"
            }
          },
          ...
          ...
        ]
      }
    }

Assuming these were set in separate variables, you would want to send the source as a string, and the mapping as json:

source: @string(json(variables('str_dyn_structure')).structure)

mapping: @json(variables('str_dyn_translator')).translator

answered Sep 4, 2019 at 19:24

VladDrak

592 bronze badges

2 Comments

JonMoz Over a year ago

@VladDrak Please could you provide an example of the complete JSON block for the copy activity? My understanding from this documentation is that the structure section should be part of the definition of the dataset, but I can't see any way to add it dynamically.

S S Over a year ago

@Jon.Mozley you need to save translator block in variable and put in dynamic content as the last line shown in answer @json(variables('str_dyn_translator')).translator. Other things are not required. In my case, I am setting mapping variable from database.

SOS · Accepted Answer · 2019-09-10 19:36:34Z

0

VladDrak - You could skip the source dynamic definition by building dynamic mapping like this:

{
  "translator": {
    "type": "TabularTranslator",
    "mappings": [
      {
        "source": {
          "type": "String",
          "ordinal": "1"
        },
        "sink": {
          "name": "dateOfActivity",
          "type": "String"
        }
      },
      {
        "source": {
          "type": "String",
          "ordinal": "2"
        },
        "sink": {
          "name": "CampaignID",
          "type": "String"
        }
      }
    ]
  }
}

edited Sep 10, 2019 at 19:36

SOS

6,5602 gold badges14 silver badges29 bronze badges

answered Sep 9, 2019 at 17:34

andromedusa

215 bronze badges

2 Comments

SOS Over a year ago

It's hard to tell if you're asking a question or proposing a different answer :-) If it's an answer, you might want to rephrase the above, so it doesn't get mistaken for a question and deleted. Otherwise, please open a new thread, see How to Ask.

andromedusa Over a year ago

It was a proposed solution that saves you the bother of the dynamic schema definition on the source.

Collectives™ on Stack Overflow

Using ADF Copy Activity with dynamic schema mapping

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related