How to send a JSON payload to Azure SQL (via Azure Data Factory)?

Question

I am unable successfully send a JSON payload over to an Azure DB Stored Procedure as i am getting this error:

Execution fail against sql server. Please contact SQL Server team if you need further support. Sql error number: 13609. Error Message: JSON text is not properly formatted. Unexpected character 'S' is found at position 0.

This is the setup of my ADF pipeline:

I am calling a REST API then placing the output into the parameters of the stored proc. The interesting thing is that when i manually copy and paste the output from the rest API in ADF into the stored proc it works without an issue. The issue only happens when i attempt to run the pipeline in ADF.

Here is a copy of the the Stored Procedure:

CREATE OR ALTER PROC whquery_editable.import_FX_rates
    @json_parameter VARCHAR(MAX)
AS
BEGIN
    DECLARE @fx_date DATE = (SELECT [value] 
                               FROM OpenJson(@json_parameter) 
                              WHERE [key] = 'date')
           ,@base_currency CHAR(3) = (SELECT [value] 
                                        FROM OpenJson(@json_parameter) 
                                       WHERE [key] = 'base')
    /* INSERT INTO xxxxx --goes here*/
    SELECT @fx_date AS fx_date
          ,@base_currency AS base_currency
          ,r.[key] AS to_currency
          ,r.[value] AS [rate]
      --i need to consider if this should be put into a temp table so that i can compare against what already exists. This would be helpful in a partial data load to fill in the gaps.
      FROM OpenJson(@json_parameter,'$."rates"') AS r
END

Here is an abbreviated version of the JSON Payload (from ADF). I also attempted to send this (abbreviated) file from a blob storage to the Stored Proc using a Lookup (in ADF) and it failed with the same error, so the contents of the file should not matter, as it is the sending of the JSON that is the problem:

{
	"success": true,
	"timestamp": 1311897599,
	"historical": true,
	"base": "EUR",
	"date": "2011-07-28",
	"rates": {
		"AED": 5.252481,
		"AFN": 61.508955,
		"ALL": 140.090308
	}
}

Lastly, this is the successful manual run of the SP using the abbreviated JSON file:

Your help would be greatly appreciated in helping me to understand why i cannot send a JSON payload over to an Azure DB Stored Proc (via ADF).

Answer 1

I was able to answer the problem and it is so simple! The "json_parameter" needs to stringified in ADF before it sends it over to Azure SQL Server. All I had to do was change:

@activity('rest_get_FX_rates').output

TO

@string(activity('rest_get_FX_rates').output)

Answer 2

In your stored procedure, you're using VARCHAR(MAX) for the JSON input. Azure SQL does support JSON, but there's no native JSON type. Instead, you use the NVARCHAR type for storing JSON. So, you may want to use NVARCHAR(MAX) instead of VARCHAR(MAX) to ensure you're handling any non-ASCII characters properly.

You're getting an error that the first character is 'S'. This might be indicating that the string sent to the stored procedure isn't just the JSON. It might be wrapped in another string or it might have some extra information added. Check to see if any additional characters or headers are added by Azure Data Factory (ADF) when sending the payload.

Manually execute the stored procedure in SQL Server Management Studio (SSMS) or Azure Data Studio with the exact payload you received from ADF. This helps ensure there's no issue with the stored procedure itself.