Download JSON Data with SSIS

In a post on the Allegient blog, I showed how you can download JSON data with PowerShell (Link no longer available due to company purchase and reorganization). Now we’ll take a look at how to download JSON data with SSIS. As a dedicated data integration tool, SSIS has many advantages over PowerShell for this type of work.

SSIS does not have a built in source component for JSON data, so you’ll have to use the Script Component. When you drag the Script Component into a data flow, you’ll be asked how you want to use it. In this case you’ll use it as a source.

Define Output Columns

Once the Script Component has been defined as a source, the output columns can be defined. For this post, the same USGS Earthquake data that was used in the “Download JSON data with PowerShell” post will serve as an example. Be careful to choose the correct data types here. This can be tedious because you have to choose the correct data types in the C# code as well, and ensure that they correspond with the SSIS types. It’s helpful to bookmark a SSIS data type translation table for reference.

Editing the Script Component

With the output columns defined, it’s time for the fun stuff – the C# code. I’m assuming the reader has some experience with the Script Component and C#, so I’m not going to go through everything in great detail. I’ll highlight the main steps below and provide a link to the complete C# script at the end of this post.

1. In the script editor, add a reference to System.Web.Extensions.
   
   
2. Generate C# classes from JSON source data. http://json2csharp.com is a great tool for this. These C# classes are the blueprints for converting JSON to .NET objects.
3. Download the JSON data using the WebClient class.
   
   C#

   public static string DownloadJson(string downloadURL) { using (WebClient client = new WebClient()) { return client.DownloadString(downloadURL); } }

   1 2 3 4 5 6 7

   public static string DownloadJson(string downloadURL)     {         using (WebClient client = new WebClient())         {             return client.DownloadString(downloadURL);         }     }

4. Deserialize the JSON string and output the desired data to the SSIS buffer. Some developers may scoff when they see the old school JavaScriptSerializer class, but using this class avoids having to load a 3rd party library like Json.NET into the Global Assembly Cache (GAC) on the server where SSIS runs. If you need to use Json.NET, you’ll likely have to jump through hoops with security and or server administrators to get it installed.
   
   C#

   public override void CreateNewOutputRows() { // Convert json string to .net object using the old school JavaScriptSerializer class JavaScriptSerializer serialize = new JavaScriptSerializer(); Earthquakes earthquakes = (Earthquakes)serialize.Deserialize(json, typeof(Earthquakes)); // Loop through array of earthquakes, outputing desired values to SSIS buffer foreach (var feature in earthquakes.features) { Output0Buffer.AddRow(); Output0Buffer.FeatureID = feature.id; Output0Buffer.DateTimeEpoch = feature.properties.time; Output0Buffer.DateTimeLocal = epoch.AddMilliseconds(feature.properties.time).ToLocalTime(); Output0Buffer.Magnitude = feature.properties.mag; Output0Buffer.Place = feature.properties.place; Output0Buffer.Longitude = feature.geometry.coordinates[0]; Output0Buffer.Latitude = feature.geometry.coordinates[1]; Output0Buffer.Depth = feature.geometry.coordinates[2]; } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   public override void CreateNewOutputRows()     {         // Convert json string to .net object using the old school JavaScriptSerializer class         JavaScriptSerializer serialize = new JavaScriptSerializer();         Earthquakes earthquakes = (Earthquakes)serialize.Deserialize(json, typeof(Earthquakes));           // Loop through array of earthquakes, outputing desired values to SSIS buffer         foreach (var feature in earthquakes.features)         {             Output0Buffer.AddRow();             Output0Buffer.FeatureID = feature.id;             Output0Buffer.DateTimeEpoch = feature.properties.time;             Output0Buffer.DateTimeLocal = epoch.AddMilliseconds(feature.properties.time).ToLocalTime();             Output0Buffer.Magnitude = feature.properties.mag;             Output0Buffer.Place = feature.properties.place;             Output0Buffer.Longitude = feature.geometry.coordinates[0];             Output0Buffer.Latitude = feature.geometry.coordinates[1];             Output0Buffer.Depth = feature.geometry.coordinates[2];         }     }

To test the script, add a Union All transformation with a data viewer on the path.

When executing the package, the results displayed in the data viewer should look something like this:

From here, you can use SSIS transformations to manipulate the data or it can be loaded directly into a destination.

Other Thoughts

This solution follows the traditional ETL or schema on write pattern, which is necessary prior to SQL Server 2016 due to the lack of JSON support. With JSON support starting with SQL Server 2016, the ELT or schema on read pattern becomes an option. This makes the SSIS package easier because it doesn’t have to perform the deserialization step. With the ELT pattern, SSIS can be used to simply load raw JSON into SQL Server.

References

Complete Script Task code