sql-serverdatabaseazure-data-factoryazure-synapsessms-17

Syncing Metadata from Azure DataFactory to SQL Server database


I am trying to sync metadata from an Azure DataFactory pipeline to a table in a SQL Server database.

The output visible in the Read Metadata activity in Azure is as follows:

{
    "childItems": [
        {
            "name": "DemoFile1",
            "type": "File"
        },

        {
            "name": "DemoFile1",
            "type": "File"
        }
    ],
    "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (UK South)",
    "executionDuration": 0,
    "durationInQueue": {
        "integrationRuntimeQueue": 0
    },
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ]
    }
}

I am using a stored procedure to transfer the metadata into the SQL Server database.

But in the SQL Server database table, I am getting the output as:

System.Collections.Generic.Dictionary`2[System.String,System.Object]

I am passing the metadata using a parameter in the stored procedure activity in DataFactory.

Parameter Name: FileName
Type: String
Value: @activity('Metadata').output.childItems

The stored procedure code is as follows:

BEGIN
 
INSERT INTO Table1 (
name, type)
 
    SELECT
 [name], [type]
    FROM OPENJSON(@FileName, '$.childitems')
    WITH (
       name    NVARCHAR(max) '$.name',
      type  NVARCHAR(max) '$.type'
    ) AS jsonValues
 
END

TIA!


Solution

  • I figured out what I was doing wrong here. The parameter value has to be stored as string (using parentheses).

    Parameter Name: FileName
    Type: String
    Value: @{activity('Metadata').output.childItems}