Sunday, March 2, 2014

Utilizing the .Net 4.0 DataContractJsonSerializer Class to Deserialize a JSON Feed in SSIS 2012

     I've written a couple of posts on how to use the JavaScriptSerializer class to deserialize a JSON feed using SSIS. Starting with the 3.5 library we get a new class that can deal with JSON, the DataContractJsonSerializer class. According to performance metrics run by the guys running the JSON.net project, this class outperforms the JavaScriptSerializer class, but under performs the JSON.net version. If you want to stay away from 3rd party libraries, the DataContractJsonSerializer seems like your best choice.

     This class uses something called DataContracts which according to Microsoft " is a formal agreement between a service and a client that abstractly describes the data to be exchanged. That is, to communicate, the client and the service do not have to share the same types, only the same data contracts." These tie into Microsoft's introduction of WCF, so this class is favored in use over the JavaScriptSerializer which was deprecated in .Net 3.5 to be resurrected again in .Net 3.5.1. There are some pros and cons to working with each of these, the most important to users of SSIS would be the ease of use. 

     For this example were going to deserialize a stream of JSON containing attributes of recently published bio-med articles at http://www.biomedcentral.com/about/api. The JSON we want to deserialize is going to look like this:

{
   "articles":[
      {
         "citation":"<i>Journal of the International Society of Sports Nutrition<\/i> 2014, <b>11<\/b>:8",
         "is_subscription":"false",
         "is_open_access":"true",
         "is_highly_accessed":"false",
         "image_credit":null,
         "article_type":"Research article",
         "article_url":"/content/11/1/8",
         "authors":"        \r\n    <span class=\"author-names\">    Roberts JD, Tarpey MD, Kass LS, Tarpey RJ and Roberts MG<\/span>\r\n    ",
         "title":"<p>Assessing a commercially available sports drink on exogenous carbohydrate oxidation, fluid delivery and sustained exercise performance<\/p>",
         "blurbText":null,
         "rss_url":"/rss",
         "thumbnailUrl":"/content/figures/1550-2783-11-8-toc.gif",
         "publication_date":"4 March 2014",
         "article_host":"www.jissn.com",
         "is_free":true
      },
      {
         "citation":"<i>BMC Genomics<\/i> 2014, <b>15<\/b>:173",
         "is_subscription":"false",
         "is_open_access":"true",
         "is_highly_accessed":"false",
         "image_credit":null,
         "article_type":"Research article",
         "article_url":"/1471-2164/15/173",
         "authors":"        \r\n    <span class=\"author-names\">    Khabudaev KV, Petrova DP, Grachev MA and Likhoshway YV<\/span>\r\n    ",
         "title":"<p>A new subfamily LIP of the major intrinsic proteins<\/p>",
         "blurbText":null,
         "rss_url":"/bmcgenomics/rss",
         "thumbnailUrl":"/content/figures/1471-2164-15-173-toc.gif",
         "publication_date":"4 March 2014",
         "article_host":"www.biomedcentral.com",
         "is_free":true
      },
      {
         "citation":"<i>BMC Medical Education<\/i> 2014, <b>14<\/b>:42",
         "is_subscription":"false",
         "is_open_access":"true",
         "is_highly_accessed":"false",
         "image_credit":null,
         "article_type":"Research article",
         "article_url":"/1472-6920/14/42",
         "authors":"        \r\n    <span class=\"author-names\">    Ma F, Li J, Liang H, Bai Y and Song J<\/span>\r\n    ",
         "title":"<p>Baccalaureate nursing Students' perspectives on learning about caring in China: a qualitative descriptive study<\/p>",
         "blurbText":null,
         "rss_url":"/bmcmedicaleducation/rss",
         "thumbnailUrl":"/content/figures/1472-6920-14-42-toc.gif",
         "publication_date":"4 March 2014",
         "article_host":"www.biomedcentral.com",
         "is_free":true
      },
      {
         "citation":"<i>BMC Cancer<\/i> 2014, <b>14<\/b>:147",
         "is_subscription":"false",
         "is_open_access":"true",
         "is_highly_accessed":"false",
         "image_credit":null,
         "article_type":"Research article",
         "article_url":"/1471-2407/14/147",
         "authors":"        \r\n    <span class=\"author-names\">    Wang J, Wei B, Albarracin CT, Hu J, Abraham SC and Wu Y<\/span>\r\n    ",
         "title":"<p>Invasive neuroendocrine carcinoma of the breast: a population-based Study from the Surveillance, Epidemiology and End Results (SEER) database<\/p>",
         "blurbText":null,
         "rss_url":"/bmccancer/rss",
         "thumbnailUrl":"/content/figures/1471-2407-14-147-toc.gif",
         "publication_date":"4 March 2014",
         "article_host":"www.biomedcentral.com",
         "is_free":true
      },
      {
         "citation":"<i>BMC Cancer<\/i> 2014, <b>14<\/b>:146",
         "is_subscription":"false",
         "is_open_access":"true",
         "is_highly_accessed":"false",
         "image_credit":null,
         "article_type":"Research article",
         "article_url":"/1471-2407/14/146",
         "authors":"        \r\n    <span class=\"author-names\">        Peng B, Zhang X, Cao F, Wang Y, Xu L, Cao L, Yang C, Li M <em>et al.<\/em><\/span>\r\n    ",
         "title":"<p>Peptide deformylase inhibitor actinonin reduces celastrol's HSP70 induction while synergizing proliferation inhibition in tumor cells<\/p>",
         "blurbText":null,
         "rss_url":"/bmccancer/rss",
         "thumbnailUrl":"/content/figures/1471-2407-14-146-toc.gif",
         "publication_date":"4 March 2014",
         "article_host":"www.biomedcentral.com",
         "is_free":true
      }
   ]
}

     The data flow for SSIS package were going to use for this example is going to look like this:

Figure 1. Data Flow Task

Ignore the union all component, I added that so I could put a data viewer on the buffer normally this would go to a data destination. By taking this JSON and pasting it in Json2Csharp.com it gave me these classes:

public class Article
{
    public string citation { get; set; }
    public string is_subscription { get; set; }
    public string is_open_access { get; set; }
    public string is_highly_accessed { get; set; }
    public object image_credit { get; set; }
    public string article_type { get; set; }
    public string article_url { get; set; }
    public string authors { get; set; }
    public string title { get; set; }
    public object blurbText { get; set; }
    public string rss_url { get; set; }
    public string thumbnailUrl { get; set; }
    public string publication_date { get; set; }
    public string article_host { get; set; }
    public bool is_free { get; set; }
}

public class RootObject
{
    public List<Article> articles { get; set; }
}

I changed all attributes that start with is_ to bool and changed image_credit and blurbText to string. These are the classes we are going to deserialize our JSON into.

     To begin lets drag a Script Component onto the design surface of the data flow task. When prompted, select source:

Figure 2. Script Component Type
     Now we need to set up our output based on the classes we generated. Open up the Script Component and go the Inputs and Outputs screen. Remove the default output and add a new output by clicking the Add Output button. Name our new output "Article". Under Article add the fields for this output by clicking on the Add Column button and applying data types that match the attributes of the Article class:

Figure 3. Script Component Inputs and Outputs Screen

     With our output columns declared were reading to start coding. Go back the the Script screen and click on the Edit Script button. Before we start any coding we need to add a reference to the System.Runtime.Serialization assembly so that we can use the DataContractJsonSerializer class. In the C# environment click on Project-->Add Reference:

Figure 4. Project Menu


On the next screen, click on the .net tab and highlight System.Runtime.Serialization, then click OK.
Figure 5. .NET Tab
With this done we can start coding. Paste the following into main.cs:

#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
  
    /// <summary>Outputs records to the output buffer</summary>
    public override void CreateNewOutputRows()
    {
        //Set Webservice URL
        string wUrl = "http://www.biomedcentral.com/webapi/1.0/latest_articles.json";
        try
        {
            //Call getWebServiceResult to return our Article attributes
             RootObject outPutResponse = GetWebServiceResult(wUrl);
            //If we get data back
            if (outPutResponse != null)
            {
                foreach (Article ar in outPutResponse.articles)
                {
                    //Output main attributes of Article
                    ArticleBuffer.AddRow();
                    ArticleBuffer.citation = ar.citation;
                    ArticleBuffer.issubscription = ar.is_subscription;
                    ArticleBuffer.isopenaccess = ar.is_open_access;
                    ArticleBuffer.ishighlyaccessed = ar.is_highly_accessed;
                    ArticleBuffer.imagecredit = ar.image_credit;
                    ArticleBuffer.articletype = ar.article_type;
                    ArticleBuffer.articleurl = ar.article_url;
                    ArticleBuffer.authors = ar.authors;
                    ArticleBuffer.title = ar.title;
                    ArticleBuffer.blurbText = ar.blurbText;
                    ArticleBuffer.rssurl = ar.rss_url;
                    ArticleBuffer.thumbnailUrl = ar.thumbnailUrl;
                    ArticleBuffer.publicationdate = ar.publication_date;
                    ArticleBuffer.articlehost = ar.article_host;
                    ArticleBuffer.isfree = ar.is_free;
                }
            }
        }
        catch (Exception e)
        {
            FailComponent(e.ToString());
        }
    }
    /// <summary>
    /// Method to return our list articles
    /// </summary>
    /// <param name="wUrl">The web service URL to call</param>
    /// <returns>An object that contains a list of Articles</returns>
  
    private RootObject GetWebServiceResult(string wUrl)
    {
      
        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
        httpWReq.Method = "GET";
        httpWReq.ContentType = "application/json";
        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
        RootObject jsonResponse = null;
        try
        {
            //Get the stream of JSON
            Stream responseStream = httpWResp.GetResponseStream();
             
                //Deserialize the JSON stream
                using (StreamReader reader = new StreamReader(responseStream))
                {
                  
                //Deserialize our JSON
                DataContractJsonSerializer sr = new DataContractJsonSerializer(typeof(RootObject));
                jsonResponse = (RootObject)sr.ReadObject(responseStream);
                }
         
        }
        //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
}
#endregion
#region JSON Classes
//Class to hold attributes of the Article
[DataContract]
public class Article
{
    [DataMember(Name = "citation")]
    public string citation { getset; }
    [DataMember(Name = "is_subscription")]
    public bool is_subscription { getset; }
    [DataMember(Name = "is_open_access")]
    public string is_open_access { getset; }
    [DataMember(Name = "is_highly_accessed")]
    public bool is_highly_accessed { getset; }
    [DataMember(Name = "image_credit")]
    public string image_credit { getset; }
    [DataMember(Name = "article_type")]
    public string article_type { getset; }
    [DataMember(Name = "article_url")]
    public string article_url { getset; }
    [DataMember(Name = "authors")]
    public string authors { getset; }
    [DataMember(Name = "title")]
    public string title { getset; }
    [DataMember(Name = "blurbText")]
    public string blurbText { getset; }
    [DataMember(Name = "rss_url")]
    public string rss_url { getset; }
    [DataMember(Name = "thumbnailUrl")]
    public string thumbnailUrl { getset; }
    [DataMember(Name = "publication_date")]
    public string publication_date { getset; }
    [DataMember(Name = "article_host")]
    public string article_host { getset; }
    [DataMember(Name = "received_events_url")]
    public string received_events_url { getset; }
    [DataMember(Name = "is_free")]
    public bool is_free { getset; }
}
//Root object that contains a List of Articles
[DataContract]
public class RootObject
{
    [DataMember(Name = "articles")]
    public List<Article> articles { getset; }
   
}
#endregion
     Now lets step through and explain a little of whats going on here. We call GetWebServiceResult and pass the URL of the web service that will deliver our JSON feed. Then we created a HttpWebRequest object that will make the request to the web service for our JSON. The stream of JSON will be returned through a HttpWebResponse object and deserialized using our DataContractJsonSerializer object. The deserialized JSON is then passed back to the calling code and looped through and sent to the ArticleBuffer for output.
     In the JSON Classes region we pasted the classes that were generated for us at Json2Csharp.com. We apply the [DataContract] attribute to the classes we have and the [DataMemberto the attributes of each class.
     Lets save, build and run the package to see our results:
Figure 6. JSON in Data Viewer
     

38 comments:

  1. As always, thanks for this tutorial, very helpful!

    ReplyDelete
  2. Hi,
    Thanks for the great post. It was just what I needed.
    But just to mention. Shouldn't the title be "...in SSIS 2010" instead of 2012.

    I am really confused with the Data Tools versions. This one looks like mine (which is 2010) but in your other post (http://dennysjymbo.blogspot.dk/2013/05/using-json-feed-as-data-source-in-ssis.html) you are using 2012 (I can see the screenshots)

    Sorry if I have misunderstood though :) Thanks again
    Have a good one.
    Pepys

    ReplyDelete
  3. The reason you are confused is that in one post I used Visual Studio 2012 to build the package. This post I used the data tools that comes with SQL Server 2012. But both utilize SSIS 2012, they are just different development environments.

    ReplyDelete
  4. I'm using BIDS 2008. Could someone please post or share package?

    ReplyDelete
  5. Hi Jim - wrote a comment, tried to post, lost it. If two appear, my mistake.

    Great tutorial, have managed to get my script to build and I get 0 errors at runtime, but my output (and a data viewer) show 0 rows passing out of the script output.

    Any ideas? I have Googled until I've been blue in the face, re-read my script a thousand times. I'm sure it's something glaringly obvious - my json feed is only 3 data elements - nothing complex. I'm relatively new to many of these concepts, so I'm surprised I've got this far, but this last hurdle has got me stumped!

    Any ideas? Something labelled incorrectly in the script which the debugger doesn't catch? Column data type issue? I'd appreciate if you could point me in the right direction - although I know how vague this all is.

    Cheers,

    Ally

    ReplyDelete
    Replies
    1. Can you post sample JSON and the code you have tried so far?

      Delete
    2. Absolutely. I didn't want to clutter up your comments section so I've stuck it in a Pastebin document, if that's ok?

      http://pastebin.com/ktf4wYTQ

      Many thanks.

      Delete
    3. Ok got this working, here is sample code just using a string and not an http call. But you can adopt it to your project Let me know if you have any more questions:



      #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.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 datas

      ///


      /// An object that contains a list of datas



      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 datas

      [DataContract]

      public class RootObject
      {

      [DataMember(Name = "data")]

      public data data { get; set; }



      }


      #endregion

      Delete
    4. Awesome - two questions though! I'm getting outputs for the 'int' columns, cpm, temperature and uptime, but I'm not getting any output of the script task for the strings - id, type, detector. I've tried changing the types on the output columns - unicode, etc, but not having much luck, Any ideas there?

      And secondly, you changed it from an http call to a string. To make this dynamic, could you give me a quick pointer on how I could select the json 'string' from the URL into the script task to be parsed?

      I appreciate all your help. Beers are on me next time you're in Central Florida!

      Delete
    5. Oh, doh! Helps if I add the id, type and detector into the outputs in the script. That bit works - I can get all the data out from a string, but no dice on the URL, which is a pain. So my other question still stands - any idea how I could pass in that string?

      Delete
    6. Cant you just use the original code you had but change the JSON class code and the foreach loop code to match whats it my code? I just used the string for my own testing because I have no access to your webservice.

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. First off, thank you for this awesome post. No way I would have been able to merge the C# & SSIS worlds without great posts like yours. My OOP skills are pretty rusty but I'm guessing the answer here is something that will stick out like a sore thumb to someone of your experience.

    My dilemma appears to be that the API I'm querying is only returning a single array of JSON objects as opposed to an array of JSON arrays. http://json2csharp.com/# indicates only the following class:

    public class RootObject
    {
    public string building { get; set; }
    public string building_additional { get; set; }
    public string street { get; set; }
    public string city { get; set; }
    }

    when fed these results I get from the Simple REST Client plugin after querying the URL:

    [{"building":"Cinco Ranch","building_additional":"49","street":"200 Highland Knolls Dr","city":"259"},
    {"building":"Clear Lake","building_additional":"53","street":"100 South Ponds","city":"271"},
    {"building":"Cypress","building_additional":"11","street":"1300 FM 1960 W","city":"253"}]

    Figured that taking the foreach loop out would solve my problem but alas I don't get any errors but I also don't get any data so I'm clearly missing something.

    My code (minus error handling since it's no longer throwing errors) :

    public class ScriptMain : UserComponent
    {
    public override void CreateNewOutputRows()
    {
    string Token = Variables.ScalableRESTToken;
    string BaseURL = Variables.ScalableRESTURL;
    string URL = BaseURL + "query?token=" + Token + "&table=cmn_location&columns=building%2C+building_additional%2C+street%2C+city&order=building&format=json";

    //Call getWebServiceResult to return our Article attributes
    RootObject outPutResponse = GetWebServiceResult(URL);

    if (outPutResponse != null)
    {
    LocationsBuffer.AddRow();
    LocationsBuffer.building = outPutResponse.building;
    LocationsBuffer.buildingadditional = outPutResponse.building_additional;
    LocationsBuffer.street = outPutResponse.street;
    LocationsBuffer.city = outPutResponse.city;
    }
    }

    private RootObject GetWebServiceResult(string wUrl)

    {
    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    httpWReq.Method = "GET";
    httpWReq.ContentType = "application/json";
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    RootObject jsonResponse = null;

    //Get the stream of JSON
    Stream responseStream = httpWResp.GetResponseStream();

    //Deserialize the JSON stream
    using (StreamReader reader = new StreamReader(responseStream))
    {
    //Deserialize our JSON
    DataContractJsonSerializer sr = new DataContractJsonSerializer(typeof(RootObject));
    jsonResponse = (RootObject)sr.ReadObject(responseStream);
    }

    return jsonResponse;
    }
    }

    #region JSON Classes
    [DataContract]
    public class RootObject
    {
    [DataMember(Name = "building")]
    public string building { get; set; }
    [DataMember(Name = "building_additional")]
    public string building_additional { get; set; }
    [DataMember(Name = "street")]
    public string street { get; set; }
    [DataMember(Name = "city")]
    public string city { get; set; }
    }
    #endregion

    Thank you much for your insight.

    ReplyDelete
    Replies
    1. Thanks for your comment about my post and am glad its gotten you this far. Now I can't test this at the moment, but I think this would work. Since you are returning an array of objects, and not an individual object, I would change your GetWebServiceResult to return and array of RootObject:

      private RootObject[] GetWebServiceResult(string wUrl)

      {
      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      httpWReq.Method = "GET";
      httpWReq.ContentType = "application/json";
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      RootObject[] jsonResponse = null;

      //Get the stream of JSON
      Stream responseStream = httpWResp.GetResponseStream();

      //Deserialize the JSON stream
      using (StreamReader reader = new StreamReader(responseStream))
      {
      //Deserialize our JSON
      DataContractJsonSerializer sr = new DataContractJsonSerializer(typeof(RootObject[]));
      jsonResponse = (RootObject[])sr.ReadObject(responseStream);
      }

      return jsonResponse;
      }
      }

      Then when you loop through it to push data to the output buffer:

      //Call getWebServiceResult to return our Article attributes
      RootObject[] outPutResponse = GetWebServiceResult(URL);

      if (outPutResponse != null)
      {
      foreach(RootObject root in outPutResponse)
      {
      LocationsBuffer.AddRow();
      LocationsBuffer.building = root.building;
      LocationsBuffer.buildingadditional = root.building_additional;
      LocationsBuffer.street = root.street;
      LocationsBuffer.city = root.city;
      }
      }

      If this doesn't work, I'll hop on my laptop when I get home to help you out further.

      Delete
    2. You sir, are a rock star. I mucked things up pretty bad trying out different combinations of things (I apparently don't understand the difference between a list and an array) but your simplification got it all working again.

      Thank you again.

      Delete
  8. Really appreciate your blog, I feel like I'm extremely close to doing exactly what is needed. That said, I'm getting the following error:

    Error: 0x1 at Data Flow Task, Error Getting Data From Webservice!: System.Runtime.Serialization.InvalidDataContractException: Type 'RootObject' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute. If the type is a collection, consider marking it with the CollectionDataContractAttribute. See the Microsoft .NET Framework documentation for other supported types

    Any ideas?

    JSON comes from this: https://itunes.apple.com/lookup?id=284910350

    And I used json2csharp to define the classes.

    ReplyDelete
    Replies
    1. Ok, nevermind, I was missing the [DataContract] attribute, as it clearly stated in the error. Didn't catch that. New error I will work on troubleshooting:

      [Error Getting Data From Webservice!] Error: System.NullReferenceException: Object reference not set to an instance of an object.
      at ScriptMain.CreateNewOutputRows() in c:\Users\uuuu\AppData\Local\Temp\Vsta\6a899b87735f4318ab32b826d5c6c987\main.cs:line 55

      Delete
    2. Hey Kyle, can you try posting your code so I can see where you're at. Thanks.

      Delete
  9. This comment has been removed by the author.

    ReplyDelete
  10. Great Article, Thanks. I need to use this in my upcoming project.

    Thanks

    ReplyDelete
  11. Thanks for this it has been very helpful but I am not getting the data from the stream into my data object. I tried to use your example "as is" just to get my SSIS package working before I modified it but the biomedcentral API doesn't exist in that that location any more and I couldn't find an updated version that I could just use without access keys.

    The error I am getting is:

    Error: 0x1 at Data Flow Task, Error Getting Data From Webservice!: System.NullReferenceException: Object reference not set to an instance of an object.
    at ScriptMain.CreateNewOutputRows()

    When I debug I see that

    jsonResponse = (RootObject)sr.ReadObject(responseStream);

    has my data object as NULL.

    so when it gets to if (outPutResponse != NULL) that is true so it goes to the foreach command and outPutResponse.data is NULL and then I move to the catch exception.

    Any suggestions on what I am doing wrong? I tried to include all my script but it was rejected for being to many characters.

    ReplyDelete
    Replies
    1. I have created a post that has the full script.

      http://stackoverflow.com/questions/43792761/ssis-script-component-json-datacontractjsonserializer

      Thanks!

      Delete
    2. Ok I posted a response. I hope this fixes your issue.

      Delete
  12. This is my first ssis project- and you have been a huge help. thanks. My watch between the script and unin is empty.

    The jsonResponse seams to have data but RegionBuffer seems to be empty.

    Files in DropBox https://www.dropbox.com/sh/yg3whvzo2223hh4/AACg5UgwDjvdza3YJBNvbHzZa?dl=0

    I won't be surprised if it is fairly simple- a few of the comment on this and your other article are close but at 4 hrs I have not nailed it down.

    ReplyDelete
    Replies
    1. I was missing the " [DataMember(Name = "region_id")]"

      Delete
    2. Glad you got it working. Sorry for the late reply.

      Delete
  13. I am building a proof of concept pulling the region info ( https://esi.tech.ccp.is/latest/#!/Universe/get_universe_regions_region_id )

    one URL ( https://esi.tech.ccp.is/latest/universe/regions/?datasource=tranquility ) will give the IDs to buld the next URLS:
    https://esi.tech.ccp.is/latest/universe/regions/10000006/?datasource=tranquility

    I am stumped in the Data contract needed for the list:

    I currently have:
    //Class to hold our work group metrics
    [DataContract]
    public class RootObject
    {
    [DataMember(Name = "region_id")]
    public List region_id { get; set; }

    //[DataMember(Name = "name")]
    //public string name { get; set; }

    //[DataMember(Name = "constellations")]
    // public List constellations { get; set; }
    // public string constellations { get; set; }

    //[DataMember(Name = "description")]
    // public string description { get; set; }
    }

    http://json2csharp.com/ throws an error.

    NOTE: I plan to call: GetWebServiceResult to get the list of regions
    string wUrl = "https://esi.tech.ccp.is/latest/universe/regions/?datasource=tranquility";

    RootObject outPutResponse = GetWebServiceResult(wUrl);


    Then Loop through the list calling GetWebServiceResult with each URl built fomr the list...

    ReplyDelete
    Replies
    1. Json 2 csharp gave you an error? Can you email me the json? Jamesferrisjr at gmail

      Delete
  14. Sent the email. ('notify me' did not show in my inbox)

    ReplyDelete
  15. Hello Jim, Thanks for the above code. Can you also write a code to push/Put data to Rest API using script task.

    ReplyDelete
  16. Hello Jim, this is exactly what i needed , and for me the output seems the same, but i'm still struggling and getting error : "[Error Getting Data From Webservice!] Error: System.NullReferenceException: Object reference not set to an instance of an object at ScriptMain.CreateNewOutputRows()" Would you be able to have a look at my code please, would be greatly appreciated...

    ReplyDelete
  17. that is the JSON :
    {
    "result": [
    {
    "u_wannabe_class": "Computer",
    "asset_tag": "EUELPF0WKU5R",
    "u_os": "Microsoft Windows 10 Enterprise",
    "u_install_status": "Deployed",
    "serial_number": "PF0WKU5R"
    },
    {
    "u_wannabe_class": "Computer",
    "asset_tag": "USCLPC0LLUBY",
    "u_os": "Microsoft Windows 10 Enterprise",
    "u_install_status": "Deployed",
    "serial_number": "PC0LLUBY"
    }
    ]
    }

    ReplyDelete
  18. #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;
    using System.Runtime.Serialization;
    using System.Runtime.Serialization.Json;
    using System.Collections.Generic;
    using System.Text;
    #endregion

    ReplyDelete
  19. public override void CreateNewOutputRows()
    {

    string sysparm_display_value = Variables.sysparmdisplayvalue;
    int sysparm_limit = Variables.sysparmlimit;
    string wUrl = "https://instance.service-now.com/api/now/table/alm_asset?sysparm_limit=20&sysparm_fields=u_install_status,u_wannabe_class,u_os,serial_number,asset_tag&sysparm_query=u_install_status=202^u_wannabe_class#=Other%20hardware&sysparm_display_value=TRUE&format=json";
    try
    {
    RootObject outPutResponse = GetWebServiceResult(wUrl);
    //
    if (outPutResponse #= null)
    {
    foreach (result ar in outPutResponse.results)
    {
    resultBuffer.AddRow();
    resultBuffer.uinstallstatus = ar.u_installstatus;
    resultBuffer.assettag = ar.assettag;
    resultBuffer.uwannabeclass = ar.u_wannabeclass;
    resultBuffer.uos = ar.u_os;
    resultBuffer.serialnumber = ar.serialnumber;
    }
    }
    }
    catch (Exception e)
    {
    FailComponent(e.ToString());
    }
    }

    ReplyDelete
  20. private RootObject GetWebServiceResult(string wUrl)
    {
    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    //ABO ADDED
    httpWReq.Credentials = new System.Net.NetworkCredential ("User", "Password");
    //
    httpWReq.Method = "GET";
    httpWReq.ContentType = "application/json";
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    RootObject jsonResponse = null;

    try

    {
    Stream responseStream = httpWResp.GetResponseStream();
    using (StreamReader reader = new StreamReader(responseStream))
    {
    DataContractJsonSerializer sr = new DataContractJsonSerializer(typeof(RootObject));
    jsonResponse = (RootObject)sr.ReadObject(responseStream);
    }
    }
    //Output JSON parsing error
    catch (Exception e)
    {
    FailComponent(e.ToString());
    }
    return jsonResponse;
    }

    ReplyDelete

  21. 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

    ReplyDelete
  22. #region JSON Classes
    //Class to hold our work group metrics
    [DataContract]
    public class result
    {
    [DataMember(Name = "u_installstatus")]
    public string u_installstatus { get; set; }
    [DataMember(Name = "assettag")]
    public string assettag { get; set; }
    [DataMember(Name = "u_wannabeclass")]
    public string u_wannabeclass { get; set; }
    [DataMember(Name = "u_os")]
    public string u_os { get; set; }
    [DataMember(Name = "serialnumber")]
    public string serialnumber { get; set; }
    }

    [DataContract]
    public class RootObject
    {
    [DataMember(Name = "results")]
    public List results { get; set; }
    }
    #endregion

    ReplyDelete
  23. How would I modify this script to aggregate the responses from multiple wUrl's? I have a webservice that only outputs the latest 100 entries per get request, so to get the next list I have a separate URL that needs to be called.

    ReplyDelete