Azure Data Factory - Using Stored Procedure as a destination with Json returns error "Cannot insert the value NULL into column"

Question

I am using Copy Activity with source dataset REST API and sink Azure SQL database with storage procedure to do the insert.

I have created the storage procedure the following way:

CREATE TYPE [dbo].[JSONObjectType] AS TABLE(
[JSONObject] nvarchar NOT NULL
);

CREATE PROCEDURE [dbo].[InsertFromAPI]
@JSONTable [dbo].[JSONObjectType] READONLY
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].[FINAL_TABLE]
SELECT vv.[dateTime] as [DATETIME], CONVERT(varchar(50),JSON_VALUE(l.header,'$.entityId')) AS UFI, vv.val as VALUE, vv.unit as UNIT, GETUTCDATE() as [LAST_MODIFIED_UTC]
FROM @JSONTable t
CROSS APPLY OPENJSON(t.JSONObject, '$.List')
WITH (header nvarchar(MAX) AS JSON,
[values] nvarchar(MAX) AS JSON) l
CROSS APPLY OPENJSON(l.[values]) v
CROSS APPLY OPENJSON(v.value)
WITH ([dateTime] datetime2(0),
unit varchar(20),
val decimal(5,1)) vv;

END

The json format from the response in the Preview Feature from Data factory is the following:

Preview data
Linked service: Source
Object:

[
{
"List": {
"header": {
"timeserieSubtype": "original",
"entityId": "id1",
"resolutionId": "Hourly"
},
"values": [
[
{
"dateTime": "2020-10-01T00:00:00",
"val": 0.4,
"unit": "MWh"
},
{
"dateTime": "2020-10-01T01:00:00",
"val": 0.6,
"unit": "MWh"
}
],
[
{
"dateTime": "2020-10-02T00:00:00",
"val": 0.5,
"unit": "MWh"
},
{
"dateTime": "2020-10-02T01:00:00",
"val": 0.7,
"unit": "MWh"
}
]
]
},
"Message": "ok"
}
]

When I run the code in TSQL of inserting data with the stored procedure works fine:

DECLARE @input AS [dbo].[JSONObjectType];

INSERT INTO @input(JSONObject)
VALUES(N' {
"List": {
"header": {
"timeserieSubtype": "original",
"entityId": "id1",
"resolutionId": "Hourly"
},
"values": [
[
{
"dateTime": "2020-10-01T00:00:00",
"val": 0.4,
"unit": "MWh"
},
{
"dateTime": "2020-10-01T01:00:00",
"val": 0.6,
"unit": "MWh"
}
],
[
{
"dateTime": "2020-10-02T00:00:00",
"val": 0.5,
"unit": "MWh"
},
{
"dateTime": "2020-10-02T01:00:00",
"val": 0.7,
"unit": "MWh"
}
]
]
},
"Message": "ok"
}');

EXEC [dbo].[InsertFromAPI] @JSONTable = @input;

select * from [dbo].[FINAL_TABLE];

I use the mapping in the copy activity as follows:

When I run the Debug I get the error:

Error details
Error code 2200
Failure type User configuration issue
Details 'Type=System.Data.SqlClient.SqlException,Message=Cannot insert the value NULL into column 'JSONObject', table '@JSONTable'; column does not allow nulls. INSERT fails. The data for table-valued parameter "@JSONTable" doesn't conform to the table type of the parameter. SQL Server error is: 515, state: 2 The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=515,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=515,State=2,Message=Cannot insert the value NULL into column 'JSONObject', table '@JSONTable'; column does not allow nulls. INSERT fails.,},{Class=16,Number=8061,State=1,Message=The data for table-valued parameter "@JSONTable" doesn't conform to the table type of the parameter. SQL Server error is: 515, state: 2,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],'

I followed the approach from these posts: https://stackoverflow.com/questions/59227458/sink-json-into-sql-server-database-using-adfv2-copy-data-task-with-stored-proced
https://learn.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database#invoke-a-stored-procedure-from-a-sql-sink