azureazure-data-factory

How do I include an ADF parameter in the source data for a Copy Data activity?


In an ADF pipeline, I have a Copy Data activity. The source is a call to an API which is returning a block of JSON data, the contents of which depends on the parameter included in the call. The target is a Snowflake table which contains a variant column called JSON which will contain the returned data. So my mapping looks like:

{
    "type": "TabularTranslator",
    "mapComplexValuesToString": true,
    "mappings": [
        {
            "source": {"path": "$.data"},
            "sink": {"name": "JSON"}
        }
    ]
}

However, some of the API calls contain a key value on which the returned data will depend (e.g. the parameter call includes a department ID and the data returned are the employees in that department). That key value is NOT returned in the JSON block. So in order to properly connect the dependent JSON to the key value, that key value has to be in the table, as in:

CREATE TABLE TargetTable (key_value VARCHAR(25), json VARIANT);

My question: How do I alter my mapping TabularTranslator code to include the insert of the key_value (which is a parameter passed into the pipeline) along with the JSON?


Solution

  • To add the parameter to the source during copy, you can make use of Additional columns option in the Source of the copy activity. Alter your target table and add the required column to it.

    Create the additional column key_value and give the required parameter to it through the dynamic content expression as shown below.

    enter image description here

    In the mapping, click on import schemas and it will ask to provide the parameter value. Give any value in from your sample parameter values. In the mapping, map your key_value column from target to additional column key_value from source. For sample, I have used SQL database instead of snowflake and used sql_variant column.

    enter image description here

    Run the pipeline and pass the required value to the parameter. For demo, I have copied some random string column from source JSON to the variant column and you can see the target key_value column value getting copied from source parameter.

    enter image description here