Split and Parse Through JSON Structures using KingswaySoft

Having a JSON response that needs to be properly parsed is a common use case, which can be advanced JSON structures like multi-dimensional arrays, key-value pairs, etc. Our KingswaySoft JSON components can easily parse through them using the various features and options available to make your integrations easier. However, there could be cases in which a JSON design is such that it could have records that need to be handled separately from each other. That is, there may be some objects in a JSON array that have a specific field type, and based on that field type, a specific node could change. Now, this change, if not properly handled, could lead to data loss or other unhandled exceptions. We will be seeing one such case in this blog post. Shown below are the components that we will be using in this demonstration. They come as a part of our SSIS Productivity Pack.

• HTTP Connection Manager
• JSON Source component
• JSON Extract Component
• Premium Derived Column component
• HTTP Requester component
• Premium ADO.NET Component
• Conditional Split, an out of the box SSIS component

Now, let's take a closer look at the scenario.

Sample Data

The sample data we are considering for this case is a JSON structure, as shown below. As you can see, it consists of an array of JSON objects. The "ContentType" node dictates whether the node "Text" exists or whether the node "fileContent" has an actual download link in it.

{
   "entries":[
      {
         "id":"1145",
         "creationDate":"2020-11-23T10:09:46.624+01:00",
         "mime":"text/html",
         "encoding":"UTF-8",
         "contentType":"TEXT_ENTRY",
         "text":"Test Text Enttry",
         "fileContent":[
            
         ]
      },
      {
         "id":"3467",
         "creationDate":"2021-11-28T10:09:46.645+01:00",
         "mime":"application/pdf",
         "contentType":"ATTACHMENT",
         "fileContent":{
            "id":"7dfbf12d-102c-11ea-97b1-274e6565cff3",
            "name":"26-11-2019_Angebot120_Ramsauer_Stefan_01-01-1990_01",
            "mimetype":"application/pdf",
            "uri":"https://www.kingswaysoft.com/Frontend/Data-Sheet/SSIS-Integration-Toolkit-for-Microsoft-Dynamics-365.pdf?pdf=DataSheet-365"
         }
      }
   ]
}

If "ContentType" has the value "TEXT_ENTRY", then that record will have a "Text" node that requires to be written into a database. However, if the node "ContentTYpe" has the value "ATTACHMENT", then the "fileContent" node would have a set of values, including a URL, which should be used to download a file content to a local file. So, in order to accomplish this, the logic shown below would need to be applied:

• Parse through the JSON document
• Treat the node "FileContent" as a raw JSON structure
• Check to see if the ContentType is TEXT_ENTRY or ATTCHMENT
• Split the records into two flows: one to handle the Text content to a database table and the other to download the file

Let's see how the design would look.

Design and configuration

Now that we have seen how the sample data looks like, and how the logic needs to be applied, let's see how this can be implemented as a data flow. To get a better idea of how to proceed further, the overall flow would look as seen below.

We start with configuring an HTTP Connection manager, which has the Base URL and the authentication information to access the endpoint and to get the response from the API. And then, we use a JSON Source component to parse it. On the general page, provide the required HTTP method and query string parameters, relative path, etc., and then select the document designer page. Here, you could either import a sample JSON document from a local file (which has the response JSON saved in it) or as we have done, use the "Web" option.

As you can see above, the "fileContent" node is treated as "Raw" - This is because we would be using this later downstream to parse it properly. The Array "entries" are the output, which we shall then connect to a Conditional Split component. In this component, we provide a condition to split the parsed record into two. And then, we can name the actual output as "AttachmentOutput" and the other one as "TextOutput". Referring to our sample data, you would see that the AttachmentOutput would have a valid value "fileContent" node since it would equate to the condition as True in the Conditional Split component.

The TextOutput, on the other hand, will have an empty output for fileContent. However, it will have a string value inside the "Text" node, which we can then write to a Premium ADO.NET Destination component.

The AttachmentOutput is then attached to a JSON Extract component. Our JSON Extract component accepts a column that has a raw JSON structure and parses it out. To do that, you would first need to make sure that the document designer matches the raw JSON column, which in this case would be the raw fileContent column. You could save the fileContent JSON structure to a local file and import it into the JSON extract document designer page.

{
            "id":"7dfbf12d-102c-11ea-97b1-274e6565cff3",
            "name":"26-11-2019_Angebot120_Ramsauer_Stefan_01-01-1990_01",
            "mimetype":"application/pdf",
            "uri":"https://www.kingswaysoft.com/Frontend/Data-Sheet/SSIS-Integration-Toolkit-for-Microsoft-Dynamics-365.pdf?pdf=DataSheet-365"
         }

The second thing to note is that, on the Additional Settings page, the proper Document Input Column is chosen.

Now, this will parse out the above JSON structure, which will have a URI field that has an actual download URL. We will be using the URI that gets parsed out from here to download the file. In order to do that, we will be using an HTTP Requester component. The HTTP Requester component requires an HTTP connection manager, which will have the Base URL for the download endpoint. And it also requires a download path to a local file, which we will be provided using a Premium Derived Column component. You will also notice that we have provided a relative path as well, using a Premium Derived Column component to accommodate the HTTP requirement.

Now, when we run this package, the files in the original JSON are downloaded into the File path. And those records that have a Text entry, would be stored in a database table. We have a sample package that we have uploaded, which you could download for free and refer to, to get an idea of finer design aspects.

Conclusion

By looking at the blog post above, you could see how KingswaySoft components can be used in a flexible way to handle such complex scenarios, and by doing so, make it into a compact and simple data flow. While building an SSIS package, our components ensure that the usual hurdles involved can be easily surpassed, making the experience much simpler and more efficient.

We hope this has helped!