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!

[Edit] the question and tag the DBMS you're using. – sticky bit Jan 05 '22 at 18:55 — sticky bit, Jan 05 '22 at 18:55

Yitzhak Khabinsky · Accepted Answer · 2022-01-06T03:29:18.187

2

Please try the following solution.

Few things were missing:

Curly brackets { and }.
A comma ],
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 |
+-----------+------+

edited Jan 06 '22 at 03:29

answered Jan 05 '22 at 20:20

Yitzhak Khabinsky

18,471
2
15
21

Hi. Thanks for the reply. But I am still getting the same error. – user17843794 Jan 06 '22 at 02:37
I have updated the input with the exact input, which I am getting from the system. I want the output exactly as you have posted. – user17843794 Jan 06 '22 at 02:46
I updated the answer. While asking a question you need to provide a minimal reproducible example. It is copied as-is to SSMS, and will be runnable there. – Yitzhak Khabinsky Jan 06 '22 at 03:09

score 1 · Answer 2 · answered Jan 05 '22 at 18:51

1

Your json structure is wrong and your error cause of that. Actually you missed { and } in your json should be like below:

{"childItems": [ { "name": "DemoFile1", "type": "File" }, { "name": "DemoFile2", "type": "File" } ]}

answered Jan 05 '22 at 18:51

Farshid Shekari

2,391
4
27
47

Hi. Thanks for the reply. I have updated the input with the exact input, which I am getting from the system. Please have a relook. – user17843794 Jan 06 '22 at 02:46

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

2 Answers2