jsonsql-serverdatabaseazure-data-factoryssms-17

Syncing Metadata output from Datafactory in SQL Server database error using stored procedure: Json - Unexpected character 'S' is found at position 0


I am getting this error while trying to write the metadata output from DataFactory to a SQL Server database.

"errorCode": "2402",
"message": "Execution failed against SQL Server.
SQL error number: 13609.
Error Message: JSON text is not properly formatted. Unexpected character 'S' is found at position 0."

I am using a stored procedure in the SQL Server database.

Metadata output:

{
        "childItems": [
            {
                "name": "DemoFile1",
                "type": "File"
            },
            {
                "name": "DemoFile2",
                "type": "File"
            } ]
       "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime",
       "executionDuration": 0
}

Procedure code:

CREATE PROCEDURE prod1
    @parameter1 NVARCHAR(max)
AS
BEGIN
    INSERT INTO [dbo].[Table1] ([name], [type])
        SELECT
            name, type
        FROM 
            OPENJSON(@parameter1)
                WITH (
                      name NVARCHAR(max) '$.name',
                      type NVARCHAR(max) '$.type'
                     ) AS jsonValues
END

TIA!


Solution

  • Please try the following solution.

    Few things were missing:

    1. Curly brackets { and }.
    2. A comma ],
    3. OPENJSON(@parameter1, '$.childItems') second parameter.

    You can always check if it is a well-formed JSON via T-SQL ISJSON() function.

    SQL

    DECLARE @parameter1 NVARCHAR(max) = 
    N'{
        "childItems": [
            {
                "name": "DemoFile1",
                "type": "File"
            },
            {
                "name": "DemoFile2",
                "type": "File"
            }
        ],
        "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime",
        "executionDuration": 0
    }';
    
    IF ISJSON(@parameter1) = 1
        SELECT name, type
        FROM OPENJSON(@parameter1, '$.childItems')
        WITH (
            name NVARCHAR(max) '$.name',
            type NVARCHAR(max) '$.type'
        ) AS jsonValues
    ELSE
        THROW 50000,'JSON is not well-formed',1;
    

    Output

    +-----------+------+
    |   name    | type |
    +-----------+------+
    | DemoFile1 | File |
    | DemoFile2 | File |
    +-----------+------+