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!
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}