SSIS JSON Parser Task

Step-By-Step

In this tutorial we will learn how to parse JSON value using ZS JSON Parser Task.

Firstly, You need to Download and Install SSIS ZappySys PowerPack.

Once you finished first step, Open Visual Studio and Create New SSIS Package Project.

Drag and drop ZS JSON Parser Task to the design panel. With the installer, you will also have another free tool the ZS Logging Task and join the components with the green arrow.
SSIS JSON Parser Task - Drag

How to cerate Variables.

Let's Create Variables, In Visual Studio Right Click on Design Panel, Select Variables.
SFTP Task Operations - Create Variable

Create two variables CustomerID and CustomerName with String DataType.
SSIS JSON Parser Task - Create Variable

How to Parser Value using JSON Parser Task.

Double click on JSON Parser Task to configure it.

Set AccessMode to Direct and use the following JSON text. For value Mappings set Target with Variables and LOG-TO-OUTPUT. This means that the values will be displayed in the SSIS Output panel.

{
"CustomerID": "ALFKI",
"CustomerName": "Alfredo Koli",
"Age": 55,
"LocationInfo": {"City":"Newyork", "State":"NY", "Zip":"23234"},
"OrderIDList": [111,222,333,444,555]
}

You can select the CustomerID & CustomerName variables which we have created just above these steps in section - How to cerate Variables, you can see the same in below screenshot. SSIS JSON Parser Task - Configure

Click OK to save JSON Parser Task configure setting UI.

Now, double click on ZS Logging Task to configure it.

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.
SSIS JSON Parser Task - ZS Logging Task

Click OK to save Logging Task configure setting UI.

Finally, run the packages and check the output. If you cannot see the output, go to View in the menu and select Output.
SSIS JSON Parser Task - Execute Package

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.

Properties

Property Name

Description

Source AccessMode

Access mode for source JSON.

Options	Description
SourceVariable	Variable which holds JSON string
SourceConnection	Connection which holds JSON file path
SourceDirectValue	Direct JSON Text

JSON Path (Filter)

JSON Path which holds JSON Filter

TargetSettings

Target settings for each extracted value from JSON

LoggingMode

LoggingMode determines how much information is logged during Package Execution. Set Logging mode to Debugging for maximum log.

Articles / Tutorials

Click here to see all articles for [SSIS JSON Parser Task] category

Parse JSON using SQL Server 2014 or lower

Parse JSON using SQL Server 2014 or lower Hello, 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. Solution In this article we will learn how to parse JSON in SQL Server using […]

How to Import / Export data from Zuora API using SSIS

Introduction In this post, we will learn how to call Zuora API using SSIS. We will go through steps to read data from Zuora and load into SQL Server using SSIS REST API Task and SSIS JSON Source Connector . At the end of this article, you will find a complete sample (dtsx file). Tasks/Components in […]

Content

Video Tutorial