How to use the JSON Parser Task in SSIS

[daniel]

Introduction

This article will show you how to use the SSIS JSON Parser Task, a free component.

Prerequisites

• SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from this site.

Steps

1. Add the JSON Parser Task: Drag and drop the SSIS JSON Parser Task from the toolbox onto the Control Flow.

2. Provide the JSON Source: It can be in a variable, a local file, or a direct value in the component. In our example, we are using a JSON file; this is the example:

   {
     "store": {
       "books": [
         {
           "category": "reference",
           "author": "Bob",
           "title": "hellooo1",
           "prices": {"discount":50, "real":5},
           "sections": ["s1", "s2", "s3"]
         },
         {
           "category": "fiction",
           "author": "Sam",
           "title": "hellooo2",
           "prices": {"discount":20, "real":8},
           "sections": ["s4", "s1", "s3"]
         },
         {
           "category": "science",
           "author": "Steve",
           "title": "hellooo3",
           "tag": "1bcd",
           "prices": {"discount":0, "real":10},
           "sections": ["s9", "s2", "s3"]
         }
       ]
     }
   }
   

3. Select a JSON Path Filter: If needed, use a JSONPath expression to filter the data. For example, to get the first book from the object book inside the object store: $.store.books[0]. For more information, click here.

4. Extract Values: Select the values you want to extract, either single or multiple values. Specify the JSONPath expression or type the property name you wish to extract from the JSON Document. You can save the values in a variable or show them in the log result when you run the package.

5. Test the Configuration: Press the Test button to check the results. Then press ok to save the configuration.
   

   

6. Connect to a Logging Task: Connect the task to a Logging task to check the variable results.
   

7. Run the Package: Run the package and check the final result.
   

   

Conclusion

This article explains how to use our JSON parser task. If you have any issues, please contact our support via chat on our website.

References

• SSIS JSON Parser Task