There are 2 ways to map a JSON payload in the SQL Server destination step which are the following:
The image below shows the SQL Server Destination step. The focus will be on the Column Mappings section as outlined.
1. Column Mappings Grid
A JSON Payload can be mapped manually by typing the details in the Column Mappings grid.
To map a payload using the grid, click on the highlighted plus button in the page below.
Path – The JSON Path to the value in the record.
Column – Name of the column to compare the value to.
SQL Type – The SQL-defined type. This is set to Varchar by default. Click on the highlighted drop-down arrow to choose from a list as shown in the image below.
Length – Set the maximum character length. This is only used with Varchar, NVarachar and VarBinary SQL types.
Precision – Set the number of digits in a number.
Scale – Set the number of digits to the right of the decimal point in a number.
Once configured, click on the tick button highlighted in yellow on the far right of the row to add the buttons or click the (x) button to remove the mapping.
2. JSON code
The JSON payload can be added manually by clicking on the code icon (</>) as highlighted in the below image.
The page below will open where the JavaScript code can be typed.
NB: ChatGPT can be prompted to “Create a JSON payload with this structure for each field in this list”.