Json parameter problem form Get Metadata activity to Stored Procedure

Kvaratshelya, Vladimir 5 Reputation points

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.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,103 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Kvaratshelya, Vladimir 5 Reputation points
    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
    (SELECT '[' + JSON_VALUE(value, '$.name') + ']' as fieldName, 
                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]
    0 comments No comments