none
How to do a Dynamic Column mapping in Copy Activity

    Question

  • I am copying from csv file to Azure sql table.

    for example csv file has 10 columns and Target table has 30 columns where there are no same column names , I have to map these columns dynamically using json string which can be added into mapping tab dynamic content. BUt I am not sure about the format which we have to give the mapping string.

    could anyone please help me with that

    Monday, April 30, 2018 8:30 AM

All replies

  • I think this document should help answer your question. The parameter columnMappings should do the trick that you are looking for. Let me know if you face any issues

    ____________________________________________________________________________________

    Please "Mark it as answer" if you find this answer helpful. This will help other community members



    Wednesday, May 2, 2018 12:33 AM
    Moderator
  • I am facing this same issue. My column mapping is driven from a configuration table in my database. I am trying to pass my column mapping into my copy activity, but I don't know what format it should be in.  I'm using the dynamic content window on the Mapping tab of the Copy activity, so my code looks like this:

    "translator": {
        "value": "@json(item().ColumnMapping)",
         "type": "Expression"
     }

    In my database, I don't know what format "ColumnMapping" should be. I've tried several but its not working.

    Tried like this:

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


    and like this:

    {
        "view.url": "url",
        "view.name": "name"
    }


    and like this:

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

    and several other ways....

    Monday, July 2, 2018 10:55 PM
  • Hi Jeffrey and Harish,

    To dynamically map column in copy activity, please define a parameter with type 'Object' in pipeline:

    "parameters": {

    "columnMapping": {

    "type": "Object"

    }

     }

     

    and reference this parameter in copy activity like:

    "translator": {

    "value": "@pipeline().parameters.columnMapping",

    "type": "Expression"

    }

    Please notice that you need to pass a JSON value to parameter. For example, I pass

    {

    "type": "TabularTranslator",

    "columnMappings": {

    "Prop_0": "Prop_0",

    "Prop_1": "Prop_1", "Prop_2": "Prop_2"

    }

    }

    to the parameter 'columnMapping'.

    Thanks.

    • Edited by Wang Zhang Monday, July 9, 2018 8:12 AM
    Monday, July 9, 2018 8:01 AM