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.
- In the script editor, add a reference to System.Web.Extensions.
- 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.
- Download the JSON data using the WebClient class.
1234567public static string DownloadJson(string downloadURL){using (WebClient client = new WebClient()){return client.DownloadString(downloadURL);}} - 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.
1234567891011121314151617181920public override void CreateNewOutputRows(){// Convert json string to .net object using the old school JavaScriptSerializer classJavaScriptSerializer serialize = new JavaScriptSerializer();Earthquakes earthquakes = (Earthquakes)serialize.Deserialize(json, typeof(Earthquakes));// Loop through array of earthquakes, outputing desired values to SSIS bufferforeach (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.
Great Post Chris,
I did an almost identical thing but my just keeps failing. I get a count on the (what for you would be features, which equals one(1)) but when I enter the foreach loop to add the rows it fails on AddRow.
Object reference not set to an instance of an object.
at ScriptMain.CreateNewOutputRows()
at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers, OutputNameMap OutputMap)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
I found out the object is not getting serialized, but I still have no clue as to why.
Glad you like the post. The first thing I’d check is if null values in your source data are nullable in your class. Class properties can be marked as nullable with a question mark as shown in the code below.
public int? nullableInteger { get; set; }
Thank you, that was the most simple guide I ever came across. I bet will help loads of beginners.
Another good article found related to this
.
https://zappysys.com/products/ssis-powerpack/ssis-json-integration-pack/
Thank you so much.
Thank you, Nancy. I have another blog post planned that uses SSIS to load JSON, but the heavy lifting is done with the new JSON functions in SQL Server 2016 instead of in SSIS. I prefer that technique, because it doesn’t require the tedious data type translations from C# to SSIS to SQL Server.
Thanks Chris for your contribution – FYI, the blog link is no longer valid to reference your powershell script, however i did see you have it up on your github for those who need it here:
https://gist.github.com/cjkoester/bb9dffdd80a2650c3613cad82df241b9
Cheers
Hey, John. Thank you for bringing this to my attention and linking to the Gist. I removed the link, because the Allegient site and blog are now gone due to the company being purchased by DMI.
Amazing post. You saved me days which would have been wasted in trying to deploy newtonsoft.json in GAC of azure 🙂 :(. Keep it up. Amazing insight and elegant post
Thank you very much for the feedback! I’m happy to hear that the information was helpful.
Hi Chris! Nice and simple guide and well made!
I’m not that experienced with C# yet, still learning and was wondering how you would go about in your script if the URL needs basic authentication?
Cheers,
Mark
Great post! Thanks Chris!
I’m really new to C# and was wondering how you would bake basic authentication in to your script?
Can this method handle large JSON response from a restapi , say 100000+ records?
I would prefer not using zippy or newtonsoft for bulk upload to SQLServer and hope that this solution can work for large JSON