Parse JSON data within SQL Server Integration Services Package?

Question

I'm trying to set up an SSIS job that will pull a JSON-encoded mailing list from MailChimp, compare it to a list of customers in our CRM database (SQL Server), and upload via JSON any new customers not already there. I can't seem to find anything on serializing/deserializing JSON within SSIS, other than writing a script task, and it seems that I can't import the .Net serialization libraries into a script. Any suggestions? Thanks in advance!

Answer 1

Couple things to address here:

First, your problem with adding new libraries in the scripting component. I assume you're using VS 2008 to do your SSIS development and want to use the .net 3.5 library to do this. You go to project, add reference and you don't see any of the dll's you need. This may be in part that you're using windows 7 and the compact 3.5 framework. .net 3.5.1 comes with Windows 7, you just have to enable it. Go to control panel, programs and features. In that screen you will see Turn Windows features on or off, click on that. In that window check Microsoft .NET Framework 3.5.1, this way take a few minutes to run. Once it finishes look for a directory similar to these C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETFramework\v3.5\Profile\Client and C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\v3.5. Between these 2 directories, you will find any dll you will need for serialization/deserializtion of JSON. These can be added to your project by going to Project-->Add Reference-->Browse Tab, then navigate to the v3.5 directory and select the dlls you need(System.Web.Extensions.dll(v3.5.30729.5446)is used in this example).

To get JSON from a web service, deserialize it, and send the data to your CRM database, you will have to use a script component as a source in your data flow and add columns to your output buffer that will be used to hold the data coming from the JSON feed(on the Input and Output screen). In the code, you will need to override the CreateNewOutputRows method. Here is an example of how to do this:

Say Your JSON looked like this...[{"CN":"ALL","IN":"Test1","CO":0,"CA":0,"AB":0},{"CN":"ALL","IN":"Test2","CO":1,"CA":1,"AB":0}]

I would fist define a class to mirror this JSON feed attributes (and the columns you defined on the inputs and outputs screen) that will eventually hold these values once you deserialize...as such:

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; }
}

Now you need to call your web service and get the JSON feed using an HttpWebRequest and a Stream:

string wUrl = "YOUR WEB SERVICE URI";
string jsonString;
HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
Stream responseStream = httpWResp.GetResponseStream();
using (StreamReader reader = new StreamReader(responseStream))
            {
                jsonString = reader.ReadToEnd();
                reader.Close();
            }

Now we deserialize our json into an array of WorkGroupMetric

JavaScriptSerializer sr = new JavaScriptSerializer();
WorkGroupMetric[] jsonResponse = sr.Deserialize<WorkGroupMetric[]>(jsonString);

After deserializing, we can now output the rows to the output buffer:

 foreach (var metric in jsonResponse)
        {

            Output0Buffer.AddRow();
            Output0Buffer.CN = metric.CN;
            Output0Buffer.IN = metric.IN;
            Output0Buffer.CO = metric.CO;
            Output0Buffer.CA = metric.CA;
            Output0Buffer.AB = metric.AB;
        }

Here is what all the code put together would look like(I have a step by step example here):

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;


 [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
 public class ScriptMain : UserComponent
 {

public override void CreateNewOutputRows()
{
    string wUrl = "YOUR WEB SERVICE URI";

    try
    {
        WorkGroupMetric[] outPutMetrics = getWebServiceResult(wUrl);

        foreach (var metric in outPutMetrics)
        {

            Output0Buffer.AddRow();
            Output0Buffer.CN = metric.CN;
            Output0Buffer.IN = metric.IN;
            Output0Buffer.CO = metric.CO;
            Output0Buffer.CA = metric.CA;
            Output0Buffer.AB = metric.AB;
        }

    }
    catch (Exception e)
    {
        failComponent(e.ToString());
    }

}


private WorkGroupMetric[] getWebServiceResult(string wUrl)
{

    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    WorkGroupMetric[] jsonResponse = null;

    try
    {

        if (httpWResp.StatusCode == HttpStatusCode.OK)
        {

            Stream responseStream = httpWResp.GetResponseStream();
            string jsonString;

            using (StreamReader reader = new StreamReader(responseStream))
            {
                jsonString = reader.ReadToEnd();
                reader.Close();
            }

            JavaScriptSerializer sr = new JavaScriptSerializer();
            jsonResponse = sr.Deserialize<WorkGroupMetric[]>(jsonString);

        }

        else
        {
            failComponent(httpWResp.StatusCode.ToString());

        }
    }
    catch (Exception e)
    {
        failComponent(e.ToString());
    }
    return jsonResponse;

}

private void failComponent(string errorMsg)
{
    bool fail = false;
    IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
    compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);

}
}
 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; }
 }

This can now be used as an input for a data destination (your CRM database). Once there you can use SQL to compare the data and find mismatches, send the data to another script component to serialize, and send any updates you need back to the web service.

OR

You can do everything in the script component and not output data to the output buffer. In this situation you would still need to deserialze the JSON, but put the data into some sort of collection. Then use the entity framework and LINQ to query your database and the collection. Determine what doesn't match, serialize it, and send that to the web service in the same script component.