azureazure-data-factorytabular

With TabularTranslator in ADF how do I map array to string?

I cannot map Array[0] to string with TabularTranslator. Any suggestion?

I would like to translate

Source (MSSQL) : SELECT JSON_ARRAY('Test') as category

to

Sink (JSON) : "category" : ["Test"]

What ever i try i keeps wrapping ["Test"] in double quoutes like this: "["Test"]"

I have tried many things, but my json translate schema for my copy activity is not working.

Any suggestions to solve the above? I would really not like to use Mapping dataflow for this task.

Thanks

Solution

  • You can try below approach to achieve your requirement.

    First use below MySQL query in the copy activity source. This will generate required JSON content as JSON string.

    SELECT JSON_OBJECT('category',JSON_ARRAY('Test','hi'));

    enter image description here

    As you want to Generate JSON file, use a delimited text dataset to generate the required JSON file. Create a delimited text dataset with following configurations and give the name as <filename>.json.

    enter image description here

    Give this dataset as sink of the copy activity. Now, import the mapping and it will show this as string column. But with the above configurations in the dataset, it will give the JSON file with desired result.

    enter image description here

    Now, run the copy activity and the JSON will be generated in the given location.

    enter image description here

    You need to create JSON dataset for the further usage of this JSON file in ADF.