Help > Tasks > JSON Parser Task >
SSIS JSON Parser Task
PreviousNext

SSIS JSON Parser Task can be used to extract single or multiple values out of JSON document in control flow. You can extract value from content stored in direct value, SSIS Variable or File. Simply specify JSONPath expression or type property name you wish to extract from JSON Document.

Download SSIS PowerPack

Content

Video Tutorial

Coming Soon...

Step-By-Step

In this tutorial we will learn how to parse JSON value using ZS JSON Parser Task.
  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
  2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
  3. 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.

  1. Let's Create Variables, In Visual Studio Right Click on Design Panel, Select Variables.
    SFTP Task Operations - Create Variable
  2. Create two variables CustomerID and CustomerName with String DataType.
    SSIS JSON Parser Task - Create Variable

How to Parser Value using JSON Parser Task.

  1. Double click on JSON Parser Task to configure it.
  2. 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]
    }
    
    SSIS JSON Parser Task - Configure
  3. Click OK to save JSON Parser Task configure setting UI.
  4. Now, double click on ZS Logging Task to configure it.
  5. 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
  6. Click OK to save Logging Task configure setting UI.
  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 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.

Setting UI

SSIS JSON Parser Task - Test JSON Path Settings
SSIS JSON Parser Task - Test JSON Path Settings

See Also

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

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

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 […]



Copyrights reserved. ZappySys LLC.