In this blog post, we will discuss how to import nested JSON objects into SQL Server using SQL Server Integration Services (SSIS). We will provide step-by-step instructions and code examples to help you understand the process.

Example #1: Importing a Simple Nested JSON Object

Let’s start with a simple example of a nested JSON object. The JSON file contains a root level object with a nested object called “links”. The “links” object has five attributes: “self”, “first”, “last”, “next”, and “prev”. We will follow the same procedures as mentioned in a previous tip to load the file.

First, we need to create a data flow task and add a script component to source the JSON file. Then, we add the output columns for the JSON object. The columns “First”, “Last”, “Next”, “Prev”, and “Self” are added as output columns with the datatype string.

Next, we define a class to store the value of the “links” object at runtime. We create two classes: “LinkSubItem” to store the attributes of the “links” object, and “Petition” to represent the root level JSON object. The “Petition” class has a property to store the values of the “links” object.

Once the classes are defined, we can deserialize the JSON file into an object of type “Petition”. We can then access the attributes of the “links” object using .NET libraries.

Here is an example of the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace PetitionNamespace
{
   class Petition
   {
      public LinkSubItem links { get; set; }
   }

   public class LinkSubItem
   {
      public string self { get; set; }
      public string first { get; set; }
      public string last { get; set; }
      public string next { get; set; }
      public string prev { get; set; }
   }
}

After the successful execution of the package, we can see the imported data in the data pipeline.

Example #2: Importing a Complex Nested JSON Object

In this example, we will look at a more complex nested JSON file. The root level object contains a single object called “data”. The “data” object has an array of “petition” objects. Each “petition” object has three attributes: “type”, “id”, and “links”. The “links” attribute is of type object.

To deserialize this JSON, we need to define three classes: “DataItem” to represent the root level object, “DataSubItem” to represent each item in the array, and “Selflink” to represent the “links” item.

Once the classes are defined, we can deserialize the JSON file and store the file content of type “DataItem”. The “DataItem” object contains a collection of “DataSubItems”. We can iterate through the “DataSubItems” to collect the details of the attributes.

Here is an example of the code:

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;

namespace PetitionNamespace
{
   public class DataItem
   {
      public Collection<DataSubItem> data { get; set; }
   }

   public class DataSubItem
   {
      public string type { get; set; }
      public string id { get; set; }
      public Selflink links { get; set; }
   }

   public class Selflink
   {
      public string self { get; set; }
   }
}

After deserializing the JSON file, we can extract the data contents and store them in the SQL Server database.

Summary

In this blog post, we have discussed how to import nested JSON objects into SQL Server using SSIS. We have learned about deserializing nested JSON into JSON runtime objects. By following the provided examples and code snippets, you can easily import and process nested JSON data in SQL Server.