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

Question

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!

Answer 1

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 |
+-----------+------+