Json parameter problem form Get Metadata activity to Stored Procedure

Question

Json parameter problem form Get Metadata activity to Stored Procedure

Kvaratshelya, Vladimir 5

I have a pipeline with Metadata Activity, which looks up the parquet file structure and passes it to the stored procedure, which creates table in serverless sql pool.

User's image

Json parameter is

@string(activity('Get Metadata1').output.structure)

The input of Stored procedure look like this:

User's image

But, it doesn't create the table because something is wrong with json parameter. When I pass it as string in stored procedure, it works.

User's image

Any help is appreciated.

KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2023-05-11T03:25:40.4633333+00:00

@Kvaratshelya, Vladimir

Thanks for using Microsoft Q&A forum and posting your query.

Could you please help share the complete output (not image but json payload) of your GetMetadata activity and the dynamic expression for external_table parameter so that we can repro and share detailed steps?

Thanks

Kvaratshelya, Vladimir 5

Here is the Get Metadata output:

{
    "itemName": "AUTHORITY.parquet",
    "structure": [
        {
            "name": "AUTH_ID",
            "type": "Decimal"
        },
        {
            "name": "U_VERSION",
            "type": "String"
        },
        {
            "name": "USER_ID",
            "type": "String"
        },
        {
            "name": "AUTH_NAME",
            "type": "String"
        },
        {
            "name": "AUTH_PASSWORD",
            "type": "String"
        },
        {
            "name": "TRX_STATUS",
            "type": "Decimal"
        },
        {
            "name": "AUTH_EV",
            "type": "Decimal"
        },
        {
            "name": "POSTING_DATE",
            "type": "DateTime"
        },
        {
            "name": "POSTING_USER",
            "type": "String"
        },
        {
            "name": "LAST_UPDATE_DATE",
            "type": "DateTime"
        },
        {
            "name": "LAST_UPDATE_TIME",
            "type": "DateTime"
        },
        {
            "name": "LAST_UPDATE_USER",
            "type": "String"
        },
        {
            "name": "AUTH_CR",
            "type": "Decimal"
        },
        {
            "name": "AUTH_CS",
            "type": "Decimal"
        },
        {
            "name": "AUTH_SV",
            "type": "Decimal"
        },
        {
            "name": "AUTH_INV",
            "type": "Decimal"
        },
        {
            "name": "AUTH_JV",
            "type": "Decimal"
        }
    ],
    "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (Canada Central)",
    "executionDuration": 0,
    "durationInQueue": {
        "integrationRuntimeQueue": 1
    },
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
                "meterType": "AzureIR",
                "duration": 0.016666666666666666,
                "unit": "Hours"
            }
        ]
    }
}

Stored procedure json parameter:

@string(activity('Get Metadata1').output.structure)

Stored procedure input:

{
    "storedProcedureName": "[dbo].[CreateExternalTable]",
    "storedProcedureParameters": {
        "json": {
            "value": "[{\"name\":\"AUTH_ID\",\"type\":\"Decimal\"},{\"name\":\"U_VERSION\",\"type\":\"String\"},{\"name\":\"USER_ID\",\"type\":\"String\"},{\"name\":\"AUTH_NAME\",\"type\":\"String\"},{\"name\":\"AUTH_PASSWORD\",\"type\":\"String\"},{\"name\":\"TRX_STATUS\",\"type\":\"Decimal\"},{\"name\":\"AUTH_EV\",\"type\":\"Decimal\"},{\"name\":\"POSTING_DATE\",\"type\":\"DateTime\"},{\"name\":\"POSTING_USER\",\"type\":\"String\"},{\"name\":\"LAST_UPDATE_DATE\",\"type\":\"DateTime\"},{\"name\":\"LAST_UPDATE_TIME\",\"type\":\"DateTime\"},{\"name\":\"LAST_UPDATE_USER\",\"type\":\"String\"},{\"name\":\"AUTH_CR\",\"type\":\"Decimal\"},{\"name\":\"AUTH_CS\",\"type\":\"Decimal\"},{\"name\":\"AUTH_SV\",\"type\":\"Decimal\"},{\"name\":\"AUTH_INV\",\"type\":\"Decimal\"},{\"name\":\"AUTH_JV\",\"type\":\"Decimal\"}]",
            "type": "String"
        },
        "external_schema": {
            "value": "fmdprod",
            "type": "String"
        },
        "external_table": {
            "value": "AUTHORITY.parquet",
            "type": "String"
        }
    }
}

Hope it helps ...

Vedant Desai 626 Reputation points

2023-05-15T11:09:58.3266667+00:00

Thank you for providing the output of the Get Metadata activity. Based on the provided structure, you can pass the JSON parameter to your stored procedure in Azure Synapse. Here's an example of how you can achieve this:

Variable Configuration: Create a variable in your Azure Synapse pipeline to hold the JSON parameter value. Set the variable type as a string.

Set Variable Activity: Add a Set Variable activity in your pipeline after the Get Metadata activity. In the activity settings, set the value of the variable to @json(activity('Get Metadata1').output.structure).

Execute Stored Procedure Activity Configuration: Configure the Execute Stored Procedure activity to call your stored procedure in the serverless SQL pool. Make sure you set up the appropriate parameter binding for the JSON parameter.

Parameter Binding: In the Execute Stored Procedure activity, set the value of the JSON parameter to the variable you created earlier. Use the expression @variables('YourVariableName') to bind the value of the variable to the stored procedure parameter.

In the above example, the @json() function is used to deserialize the JSON structure into an object. This is necessary if your stored procedure expects a structured JSON parameter.

Adjust the variable name (YourVariableName) and activity names (Get Metadata1) according to your specific pipeline configuration.
Kvaratshelya, Vladimir 5 Reputation points

2023-05-15T15:57:27.9866667+00:00
Hello Vedant,

I added the Set variable activity between GetMetadata and Stored procedure.

According to your explanation:

Variable Configuration: Create a variable in your Azure Synapse pipeline to hold the JSON parameter value. Set the variable type as a string.

Set Variable Activity: Add a Set Variable activity in your pipeline after the Get Metadata activity. In the activity settings, set the value of the variable to @json(activity('Get Metadata1').output.structure).

activity('Get Metadata1').output.structure cannont be converted to json directly it returns the following error:

structure:{name, type}[]

Data structure of the file or relational database table. Returned value is a list of column names and column types.

Cannot fit structure return type into the function parameter string.

If I set the varialble type as an array,

the input of stored procedure is as following:

"value": [ { "name": "AUTH_ID", "type": "Decimal" }, { "name": "U_VERSION", "type": "String" }, { "name": "USER_ID", "type": "String"

But inside the stored procedure the variable "json" returns "System.Collections.Generic.List`1[System.Object]"

I've also tried to set the variable type to string and to convert it as @json(string(activity('Get Metadata1').output.structure)) but the result is the same as it was in my first message. The table is not created.
Vedant Desai 626 Reputation points

2023-05-16T04:06:34.2333333+00:00
The error message you are getting is telling you that the structure type cannot be converted to a string. This is because the structure type is a list of objects, each of which has a name and type property. To convert the structure type to a string, you can use the following code:

string json = JsonConvert.SerializeObject(activity('Get Metadata1').output.structure);

To convert the JSON object to a JSON array, you can use the following code:

string[] jsonArray = JsonConvert.DeserializeObject<string[]>(json);

This will convert the structure type to a JSON string, which can then be passed to the stored procedure.

Once you have converted the structure type to a JSON string, you can pass it to the stored procedure as a parameter. To do this, you will need to update the Execute Stored Procedure Activity Configuration section of your pipeline. In the Parameter Binding section, set the value of the JSON parameter to the variable you created earlier. Use the expression @variables('YourVariableName') to bind the value of the variable to the stored procedure parameter.

Once you have made these changes, you should be able to successfully pass the JSON parameter to your stored procedure.

Here is an example of how you can do this:

// Create a variable to hold the JSON parameter value.

Variable myVariable = new Variable(); myVariable.Name = "MyVariable"; myVariable.Type = "String";

// Set the value of the variable to the JSON string.

myVariable.Value = JsonConvert.SerializeObject(activity('Get Metadata1').output.structure);

// Add a Set Variable activity to your pipeline.

SetVariableActivity setVariableActivity = new SetVariableActivity(); setVariableActivity.Name = "SetVariable"; setVariableActivity.Variable = myVariable;

// Add an Execute Stored Procedure activity to your pipeline.

ExecuteStoredProcedureActivity executeStoredProcedureActivity = new ExecuteStoredProcedureActivity(); executeStoredProcedureActivity.Name = "ExecuteStoredProcedure"; executeStoredProcedureActivity.Server = "YourServer"; executeStoredProcedureActivity.Database = "YourDatabase"; executeStoredProcedureActivity.StoredProcedure = "YourStoredProcedure"; executeStoredProcedureActivity.ParameterBindings.Add("@json", "@variables('MyVariable')");

// Run your pipeline.

Pipeline pipeline = new Pipeline(); pipeline.Activities.Add(setVariableActivity); pipeline.Activities.Add(executeStoredProcedureActivity); pipeline.Run();

If you have any further questions, please let me know, and if your problem is resolved then please Accept the Answer by clicking on Accept Answer and Helpful buttons.
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2023-05-17T23:57:13.9466667+00:00

@Kvaratshelya, Vladimir Thanks for your response and additional details.

How is your stored proc input parameter expected? Could you please share a sample of stored proc execution with hard coded values? I think that will be the key point which might give us pointers.

KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2023-05-11T03:25:40.4633333+00:00

@Kvaratshelya, Vladimir

Thanks for using Microsoft Q&A forum and posting your query.

Could you please help share the complete output (not image but json payload) of your GetMetadata activity and the dynamic expression for external_table parameter so that we can repro and share detailed steps?

Thanks
Vedant Desai 626 Reputation points

2023-05-15T11:09:58.3266667+00:00

Thank you for providing the output of the Get Metadata activity. Based on the provided structure, you can pass the JSON parameter to your stored procedure in Azure Synapse. Here's an example of how you can achieve this:

Variable Configuration: Create a variable in your Azure Synapse pipeline to hold the JSON parameter value. Set the variable type as a string.

Set Variable Activity: Add a Set Variable activity in your pipeline after the Get Metadata activity. In the activity settings, set the value of the variable to @json(activity('Get Metadata1').output.structure).

Execute Stored Procedure Activity Configuration: Configure the Execute Stored Procedure activity to call your stored procedure in the serverless SQL pool. Make sure you set up the appropriate parameter binding for the JSON parameter.

Parameter Binding: In the Execute Stored Procedure activity, set the value of the JSON parameter to the variable you created earlier. Use the expression @variables('YourVariableName') to bind the value of the variable to the stored procedure parameter.

In the above example, the @json() function is used to deserialize the JSON structure into an object. This is necessary if your stored procedure expects a structured JSON parameter.

Adjust the variable name (YourVariableName) and activity names (Get Metadata1) according to your specific pipeline configuration.
Kvaratshelya, Vladimir 5 Reputation points

2023-05-15T15:57:27.9866667+00:00

Hello Vedant,

I added the Set variable activity between GetMetadata and Stored procedure.

According to your explanation:

Variable Configuration: Create a variable in your Azure Synapse pipeline to hold the JSON parameter value. Set the variable type as a string.

Set Variable Activity: Add a Set Variable activity in your pipeline after the Get Metadata activity. In the activity settings, set the value of the variable to @json(activity('Get Metadata1').output.structure).

activity('Get Metadata1').output.structure cannont be converted to json directly it returns the following error:

structure:{name, type}[]

Data structure of the file or relational database table. Returned value is a list of column names and column types.

Cannot fit structure return type into the function parameter string.

If I set the varialble type as an array,

the input of stored procedure is as following:

"value": [ { "name": "AUTH_ID", "type": "Decimal" }, { "name": "U_VERSION", "type": "String" }, { "name": "USER_ID", "type": "String"

But inside the stored procedure the variable "json" returns "System.Collections.Generic.List`1[System.Object]"

I've also tried to set the variable type to string and to convert it as @json(string(activity('Get Metadata1').output.structure)) but the result is the same as it was in my first message. The table is not created.
KranthiPakala-MSFT 39,002 Reputation points Microsoft Employee

2023-05-17T23:57:13.9466667+00:00

@Kvaratshelya, Vladimir Thanks for your response and additional details.

How is your stored proc input parameter expected? Could you please share a sample of stored proc execution with hard coded values? I think that will be the key point which might give us pointers.

Answer 1


As I mentioned in my first message, the json object has been already deserialised to string @string(activity('Get Metadata1').output.structure)

My error was that the returned string is a json array, not a json structure.
@json = '[{"name":"LOCATION","type":"Decimal"},{"name":"BANK_XREF","type":"Decimal"},{"name":"U_VERSION","type":"String"}]'

In the stored procedure I have formatted it to json: 
set @json = '{"value": "' + @json + '", "type": "String"}';

And used OPENJSON to get the values:
--Create tmp table from json file
SELECT * into #tmp
FROM 
(SELECT '[' + JSON_VALUE(value, '$.name') + ']' as fieldName, 

        CASE
            WHEN JSON_VALUE(value, '$.type') = 'Single' THEN 'real'
            WHEN JSON_VALUE(value, '$.type') = 'Boolean' THEN 'bit'
            WHEN JSON_VALUE(value, '$.type') = 'Double' THEN 'float'
            WHEN JSON_VALUE(value, '$.type') = 'Int64' THEN 'bigint'
            WHEN JSON_VALUE(value, '$.type') = 'String' THEN 'nvarchar(4000)'
            ELSE JSON_VALUE(value, '$.type')  
        END AS                      fieldType
        FROM OPENJSON(@json, '$.value')
) t;

-- Concatenate column name and type from all rows into string
DECLARE @columns VARCHAR(max) 
SELECT @columns = COALESCE(@columns + ', ', '') + ISNULL(fieldName, '') + ' ' + ISNULL(fieldType, '')
FROM #tmp;

declare @external_schema_path varchar(max) = 'some path'

-- Create external table

declare @sql varchar(max)
set @sql=
'CREATE EXTERNAL TABLE [' + @external_schema + '].[' + @external_table + '] (' + @columns + ')

    WITH (
        LOCATION = '''+ @external_schema_path + '/' + @external_table + '.parquet'',
        DATA_SOURCE = [xxxxxxxxxx],
        FILE_FORMAT = [SynapseParquetFormat]
    ) 
'
exec(@sql);

Json parameter problem form Get Metadata activity to Stored Procedure

1 answer

Activity