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!
Please try the following solution.
Few things were missing:
],
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 |
+-----------+------+