Parse JSON using SQL Server 2014 or lower

Parse JSON using SQL Server 2014 or lower

logo parse jsonHello, this time we will show how to parse JSON in SQL Server 2014 or other versions. we will use a simple tool easy to use and we do not need programming knowledge.


In this article we will learn how to parse JSON in SQL Server using a Free tool that can be installed with SSIS that can be downloaded from the internet. If you do not have any experience in SSIS, do not worry. This is a great opportunity to learn. This article will not take long.


  • First, make sure that you have SQL Server.
  • Also, check to install SSIS (it is included with the SQL Server installer).
  • And finally, make sure to install SSDT

Getting Started

SQL Server 2016 and later versions include JSON support. You can work with JSON in T-SQL like you did with XML. However, it requires some programming knowledge to work with it and it is not simple if you do not have JSON or XML experience.

In this example, we will show 2 examples.

  1. First, we will show a simple example of how to parse a JSON file and get values in variables.
  2. Secondly, we will show how to get JSON values from a variable and store the variable values in SQL Server.

1. Example to get values from JSON into variables

Let’s start the first demo

  1. First of all, we will first download a free tool named SSIS JSON Parser Task. This task will help us to parse the JSON data. Once installed, drag and drop to the design pane. With the installer, you will also have another free tool. The ZS Logging Task
    ZS JSON Parser Task in SSIS

    Drag and drop json parser task

  2. Secondly, we will create two SSIS variables first that will store the JSON values. In SSDT, go to the SSIS menu and select Variables. Create two variables. CustomerID and CustomerName of type String:
    Customerid in variable ssis

    ssis variable

  3. Also, we will use the following JSON text:
  4. Using the ZS JSON parser task, map CustomerID and CustomerName to the user variables created before:
    Map json into variable in SSIS

    Mapping JSON values into variables

  5. For demonstration purposes, the age will be set to LOG-TO-OUTPUT. This means that the values will be displayed in the SSIS Output pane:
    Log variable values in JSON

    Send value to log in SSIS

  6. In addition, we will verify the values of the variables. To do that, we will use another free tool.  The ZS Logging Task that comes with the ZS JSON Parser task. This tool can display the variable values in the SSIS output (ExecutionLog) in a MessageBox a file or a variable. In this example, we will show the variable values in the SSIS output:
    Send variables to execution log

    Set values to ExecutionLog

  7. Finally, run the packages and check the output. If you cannot see the output, go to View in the menu and select Output.
    SSIS output variable values

    Check SSIS variables in log

    Also, the JSON Parser task shows that the age is 55. You can verify this value in the JSON data in step 3. Also, the Logging task is showing the CustomerId and the CustomerName values that can be verified and compared with the step 3 that contains the JSON objects and arrays.

2. Second example. Storing and showing how to parse JSON in SQL Server

In the first example, we learned how to parse JSON values and store them in SSIS variables and how to send values to the output. In this new example, we will show how to parse JSON values in SQL Server.

  1. First of all, will be to create a simple table in SQL Server to store JSON values:
  2. Secondly, we will use the following JSON data.
  3. Also, will create the variables first. The inputjson will contain the JSON data from the previous step:
    ssis variables

    ssis variables

  4. Once it is done,  will be to insert a variable this time. We will use the Insert Variable option and insert the inputjson variable that contains the JSON values. We will also use a filter to show just the 3rd book information (the first element index is 0). Path filters in JSON are out of the scope of this article, but you can read a nice article about the topic here. Press the Parse Field to parse the values. Finally, map the author, category, isbn and price variables:
    Filter to parse JSON in SQL Serveron data

    Using filters

  5. After that,  drag and drop the Execute SQL Task and join it with the JSON Parser to receive the variable values. Specify a connection with the server name and database name where the table of step 1 was created. In SQLStatement, write the following query:
    This query will insert the 4 SSIS parameters in SQL Server:
    Insert JSON values into SQL Server

    Execute SQL Task

  6. Also, in parameter mapping, specify the variable name, direction, data type and Paremeter name:
    Map SSIS variables

    Map variables into SQL

  7. Finally, run the package and you will be able to see the value stored in SQL Server book table:
    check table results

    Check results


As a conclusion, we can say that it is possible to parse JSON in SQL Server using SSIS additional tasks and it does not require to know T-SQL or code to get JSON values.


Finally, you can use the following links for more information about REST API and SQL Server:

keywords: parse JSON in SQL Server

Posted in SSIS JSON Parser Task.