Using a JSON Feed as a Data Source in SSIS

     In my work, I find myself having to deal with JSON (JavaScript Object Notation) feeds more and more. Where just a few years ago everything dealing with web services was primarily XML driven, JSON has creeped its way into greater and greater adoption in this arena.  With OO programmers its a great way for passing serialized objects among environments, and a way to get around the "fat" that comes along with using XML. For us in the data warehousing community, its a great and easy means of getting data into our data warehouses from data sources and pushing out data to applications and other data consumers (asp.net MVC). 

     To get a JSON feed into your data warehouse, using SSIS, is fairly simple and can be done by making use of a script component as a data source in your data flow. In this example I'm going to demonstrate how to read a JSON feed from a web service that represents daily metrics of a call center. These attributes consist of the call center work group being measured, the half hour interval the metrics were summed up to, the number of calls answered, the number of calls offered and the number of calls abandoned. This will be represented in this JSON string:

Figure 1. JSON String

     This comes from a ASP.NET web service I built for this example using REST. When testing your web service output I highly suggest using Simple Rest Client for chrome, its free and helps you visualize what your web service is sending out. This, in conjunction with JSONLint, will help you work out any kinks you may have while trying to serialize your JSON.

     In the URL you'll notice we have 3 parameters, the date we wish to analyze, the action we wish to call from the web service and the name of the call center work group. If this package will always load this data, we only need to make 2 of these parameters into variables in SSIS, the date and the call center work group:

Figure 2. SSIS Variables

We can set some default values here to match the example. In a real implementation your package would have some sort of mechanism to set the values at run-time. 

     Next, we need to create our script component that will bring our data into SSIS. In a data flow, drag and drop a script component onto the design surface. When prompted to pick a script component type, select source.

Figure 3. Script Component Type

On the Script screen we need to select the SSIS variables we want to use in our code:

Figure 4. Script Component Script Screen

     Since this script component is going to be our data source, we need to configure what the output is going to look like, For this, we need to go to the Inputs and Outputs screen. In the default output we need to add columns that represent the values we're going to get from our JSON feed:

Figure 5. Script Component Inputs and Outputs Screen

This output consists of WORKGROUPNAME DT_STR 50, INTERVALTIME DT_DBTIMESTAMP, CALLSOFFERED DT_I4, CALLSANSWERED DT_I4 and CALLSABANDONED DT_I4. With this done, let's go back to the Script screen and click the Edit Script button. This will bring up the development environment for the script component. Before we start coding we need to add System.Web.Extensions.dll as a reference. To do this we click on Project-->Add Reference:

Figure 6. Project Menu

This will bring up the Reference Manager window. Click on Browse in the side menu then click on the Browse button in the bottom right hand corner to search your computer for System.Web.Extensions.dll(note the location and version of this dll file may be different in your particular project depending on what version of .net/SSIS you're using):

Figure 7. Reference Manager Window

Click the check box then click the OK button. You should now see this available under  References in your project:

Figure 8. Project References

Now we can start some coding:

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Net;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Web.Script.Serialization;

#endregion

#region Class

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    /// <summary>Outputs records to the output buffer</summary>

    public override void CreateNewOutputRows()

    {

        //Get SSIS Variables

        string serviceDate = Variables.DATE;

        string workGroup = Variables.WORKGROUP;

        //Set Webservice URL

        string wUrl = "http://yourwebserviceaddress/api/Agent?date=" + serviceDate + "&action=getWorkGroupMetrics&webLogin=" + workGroup;

        try

        {

            //Call getWebServiceResult to return our WorkGroupMetric array

            WorkGroupMetric[] outPutMetrics = GetWebServiceResult(wUrl);

            //For each group of metrics output records

            foreach (var metric in outPutMetrics)

            {

                Output0Buffer.AddRow();

                Output0Buffer.WORKGROUPNAME = metric.CN;

                Output0Buffer.INTERVALTIME = Convert.ToDateTime(Variables.DATE + " " + metric.IN);

                Output0Buffer.CALLSOFFERED = metric.CO;

                Output0Buffer.CALLSANSWERED = metric.CA;

                Output0Buffer.CALLSABANDONED = metric.AB;

            }

        }

        catch (Exception e)

        {

            FailComponent(e.ToString());

        }

    }

    /// <summary>

    /// Method to return our WorkGroupMetric array

    /// </summary>

    /// <param name="wUrl">The web service URL to call</param>

    /// <returns>An array of WorkGroupMetric composed of the de-serialized JSON</returns>

    private WorkGroupMetric[] GetWebServiceResult(string wUrl)

    {

        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);

        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();

        WorkGroupMetric[] jsonResponse = null;

        try

        {

            //Test the connection

            if (httpWResp.StatusCode == HttpStatusCode.OK)

            {

                Stream responseStream = httpWResp.GetResponseStream();

                string jsonString = null;

                //Set jsonString using a stream reader

                using (StreamReader reader = new StreamReader(responseStream))

                {

                    jsonString = reader.ReadToEnd().Replace("\\", "");

                    reader.Close();

                }

                //Deserialize our JSON

                JavaScriptSerializer sr = new JavaScriptSerializer();

                //JSON string comes in with a leading and trailing " that need to be removed for parsing to work correctly

                //The JSON here is serialized weird, normally you would not need this trim

                jsonResponse = sr.Deserialize<WorkGroupMetric[]>(jsonString.Trim('"'));

            }

            //Output connection error message

            else

            {

                FailComponent(httpWResp.StatusCode.ToString());

            }

        }

        //Output JSON parsing error

        catch (Exception e)

        {

            FailComponent(e.ToString());

        }

        return jsonResponse;

    }

    /// <summary>

    /// Outputs error message

    /// </summary>

    /// <param name="errorMsg">Full error text</param>

    private void FailComponent(string errorMsg)

    {

        bool fail = false;

        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

        compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);

    }

}

#endregion

#region JSON Class

//Class to hold our work group metrics

class WorkGroupMetric

{

    public string CN { get; set; }

    public string IN { get; set; }

    public int CO { get; set; }

    public int CA { get; set; }

    public int AB { get; set; }

}

#endregion

     Now lets go through and explain some of this code (if you didn't add System.Web.Extensions.dll as a reference you will get an error in the Namespaces region for the line: using System.Web.Script.Serialization;). First of all we want to override void CreateNewOutputRows()to create our new output rows for the data flow. This will call private WorkGroupMetric[] GetWebServiceResult(string wUrl)which will call the web service, deserialize our JSON, and return an array of WorkGroupMetric to the calling method. In main.cs we define a class that will be used in deserializing the JSON feed, WorkGroupMetric. This has a number of attributes that match the name and type of the values coming in from the web service(creating classes that can conform to your JSON can be done at http://json2csharp.com by simply pasting your JSON text). Each JSON object will be loaded into an instance of this class. The calling method will then loop through the WorkGroupMetric array and create new output records for the data flow: 

  foreach (var metric in outPutMetrics)

            {

                Output0Buffer.AddRow();

                Output0Buffer.WORKGROUPNAME = metric.CN;

                Output0Buffer.INTERVALTIME = Convert.ToDateTime(Variables.DATE + " " + metric.IN);

                Output0Buffer.CALLSOFFERED = metric.CO;

                Output0Buffer.CALLSANSWERED = metric.CA;

                Output0Buffer.CALLSABANDONED = metric.AB;

            }

     Build, save and lets return to the data flow. We can create a union all that the script component can output to so we can add a data viewer to see the records. In a normal implementation this would go to a data destination:

Figure 9. Data Flow

     Let's now run the package and view the results:

Figure 10. JSON Feed in Data Viewer 

This can now be added to a data destination, in your data warehouse, where it awaits further transformations.

(For another example of this using more complex JSON, with a nested array, refer to my post where I discuss it in detail)

(For an example where I use the DataContractJsonSerializer to deal with JSON in SSIS 2012, refer to my post where I discuss it in detail)