'GET MetaData' Activity output to SQL table using ADF v2

Question

I'm using 'GetMetadata' activity in my pipelines to get all the folders and child items and item types. But this activity is giving output in the JSON format which i'm unable to store the values to a variable so that i can iterate thru them. I need to store the Folders metadata in a sql table

Get Metadata activity sample output is like below.

{
    "itemName": "ParentFolder",
    "itemType": "Folder",
    "childItems": [
        {
            "name": "ChildFolder1",
            "type": "Folder"
        },
        {
            "name": "ChildFolder2",
            "type": "Folder"
        },
        {
            "name": "ChildFolder3",
            "type": "Folder"
        }
    ],
    "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (North Europe)",
    "executionDuration": 187
}

Can some help me to store the above json output of 'Get MetaData' Activity into a sql table like below.

May I use loop in C# with sql connection ? Or do you have any other requirements? — Md Farid Uddin Kiron
Thankyou, but this has to be done using azure datafactory only. — user3588007
If the above is not possible, at least how can we iterate through Json output which are file names and folder names in my case. — user3588007
I have done this with sql server and C# using your Json model. You didn't noted in your question that it should be data factory. So didn't answer as I am not familiar with data factory feasibility. — Md Farid Uddin Kiron
@Md Farid Uddin Kiron I mentioned in the Title 'Using ADF v2'. Also i'm searching if there is any way to run C# code inside azure datafactory. Also waiting for someone's answer. — user3588007

DC07 DC07 · Accepted Answer · 2019-10-23T11:02:17

The easiest way to do this is pass the Get MetaData output as a string to a stored proc and parse it in your sql db using OPENJSON.

This is how to convert the output to a string. @string(activity('Get Metadata').output)

Now you just pass that to a stored proc and then use OPENJSON to parse it.

I have seen many others doing this using ADF foreach, however if you have 1000s of files/folders you will end up paying a lot for this method overtime. (each loop counts as an activity)

'GET MetaData' Activity output to SQL table using ADF v2

1 Answers