Hey all,

Does anyone have any experience of parsing JSON in C#? Specifically for SQL Integration Services, but that is kind of irrelevant.

What I’ve done is created an ETL task which parses a string/JSON into a table (see screenshots attached). I can parse a string from the Unit /j page no problem, in the format;

string wUrl = "{\"data\":{ \"id\":\"42000001\",\"type\":\"4\",\"detector\":\"SI29BG\",\"cpm\":11,\"temperature\":32.00,\"uptime\": 1409}}";

But when I try to parse using the URL;

string wUrl = "http://10.0.0.81/j.json";

I get no outputs from my Script. Any ideas what I’m missing?

C# script below;

#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.IO;
using System.Runtime.Serialization.Json;
using System.Runtime.Serialization;
using System.Collections.Generic;

using System.Text;
#endregion
#region Class

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    #region Methods
    /// Outputs records to the output buffer
    public override void CreateNewOutputRows()
    {
        //Set Webservice URL

        string wUrl = "{\"data\":{ \"id\":\"42000001\",\"type\":\"4\",\"detector\":\"SI29BG\",\"cpm\":11,\"temperature\":32.00,\"uptime\": 1409}}";

        try
        {
            //Call getWebServiceResult to return our data attributes
            RootObject outPutResponse = GetWebServiceResult(wUrl);

            //If we get data back
            if (outPutResponse != null)
            {
                //Output main attributes of data

                dataBuffer.AddRow();
                dataBuffer.id = outPutResponse.data.id;
                dataBuffer.type = outPutResponse.data.type;
                dataBuffer.detector = outPutResponse.data.detector;
                dataBuffer.cpm = outPutResponse.data.cpm;
                dataBuffer.temperature = outPutResponse.data.temperature;
                dataBuffer.uptime = outPutResponse.data.uptime;

            }

        }

        catch (Exception e)
        {

            FailComponent(e.ToString());

        }

    }

    /// 
    /// Method to return our list data
    /// 

    /// An object that contains a list of data

    private RootObject GetWebServiceResult(string wUrl)
    {
        RootObject jsonResponse = null;

        //Deserialize our JSON
        using (var ms = new MemoryStream(Encoding.Unicode.GetBytes(wUrl)))
        {
            DataContractJsonSerializer sr = new DataContractJsonSerializer(typeof(RootObject));
            jsonResponse = (RootObject)sr.ReadObject(ms);
        }

        return jsonResponse;

   }
    /// 

    /// Outputs error message

    /// 

    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
}
#endregion
#region JSON Classes

//Class to hold attributes of the data

[DataContract]

public class data
{

    [DataMember(Name = "id", Order = 0)]
    public string id { get; set; }

    [DataMember(Name = "type", Order = 1)]
    public string type { get; set; }

    [DataMember(Name = "detector", Order = 2)]
    public string detector { get; set; }

    [DataMember(Name = "cpm", Order = 3)]
    public int cpm { get; set; }

    [DataMember(Name = "temperature", Order = 4)]
    public double temperature { get; set; }

    [DataMember(Name = "uptime", Order = 5)]
    public int uptime { get; set; }

}

//Root object that contains a List of data
[DataContract]
public class RootObject
{

    [DataMember(Name = "data")]
    public data data { get; set; }

}

#endregion 

Any ideas would be appreciated.

I’m eventually going to roll this data from the monitor into SQL Analysis Services and my OLAP Cube – if I can get this table loaded from the json feed!

Thanks,

Ally