Saturday, November 23, 2013

Deserializing a JSON Feed with Nested Arrays in SSIS

     I thought I would expand upon the post I wrote about consuming JSON feeds in SSIS with a more complex piece of JSON. The JSON I used in the previous post was rather simple, but got to the general strategy of how to get SSIS to work with JSON. In this post I'd like to use JSON that have nested arrays and how we can deal with them in SSIS.  I found a site online that pushes out JSON feeds of research paper metadata at http://researchr.org/ . They have sample links such as  http://researchr.org/api/publication/HemelKGV-2010 which provides JSON that looks like this:

{
   
"abstract":"The realization of model-driven software development requires effective techniques for implementing code generators for domain-specific languages. This paper identifies techniques for improving separation of concerns in the implementation of generators. The core technique is code generation by model transformation, that is, the generation of a structured representation (model) of the target program instead of plain text. This approach enables the transformation of code after generation, which in turn enables the extension of the target language with features that allow better modularity in code generation rules. The technique can also be applied to \u2018internal code generation\u2019 for the translation of high-level extensions of a DSL to lower-level constructs within the same DSL using model-to-model transformations. This paper refines our earlier description of code generation by model transformation with an improved architecture for the composition of model-to-model normalization rules, solving the problem of combining type analysis and transformation. Instead of coarse-grained stages that alternate between normalization and type analysis, we have developed a new style of type analysis that can be integrated with normalizing transformations in a fine-grained manner. The normalization strategy has a simple extension interface and integrates non-local, context-sensitive transformation rules. We have applied the techniques in a realistic case study of domain-specific language engineering, i.e. the code generator for WebDSL, using Stratego, a high-level transformation language that integrates model-to-model, model-to-code, and code-to-code transformations. ",
   
"lastpage":402,
   
"type":"article",
   
"url":"http://researchr.org/publication/HemelKGV-2010",
   
"firstpage":375,
   
"issuenumber":"3",
   
"journal":"sosym",
   
"id":"c6ab7ca9-2764-4354-964b-ba8c3244bd88",
   
"authors":[
      
{
         
"person":{
            
"id":"947901fa-e910-454b-8546-c514c5a191be",
            
"fullname":"Zef  Hemel",
            
"key":"zefhemel",
            
"url":"http://researchr.org/profile/zefhemel"
         
},
         
"alias":{
            
"id":"7816809a-e2e6-42c6-8c44-481a821fb0bd",
            
"name":"Zef Hemel",
            
"key":"zef-hemel",
            
"url":"http://researchr.org/alias/zef-hemel"
         
}
      
},
      
{
         
"person":{
            
"id":"6e29bb96-28c9-4c76-915d-160ef1947602",
            
"fullname":"Lennart C. L. Kats",
            
"key":"lennartclkats",
            
"url":"http://researchr.org/profile/lennartclkats"
         
},
         
"alias":{
            
"id":"f6b6789b-a32a-417c-a90e-c37a12f25728",
            
"name":"Lennart C. L. Kats",
            
"key":"lennart-c.-l.-kats",
            
"url":"http://researchr.org/alias/lennart-c.-l.-kats"
         
}
      
},
      
{
         
"person":{
            
"id":"f50a3666-08fe-4b30-9734-181aed455bb1",
            
"fullname":"Danny M.  Groenewegen",
            
"key":"dannymgroenewegen",
            
"url":"http://researchr.org/profile/dannymgroenewegen"
         
},
         
"alias":{
            
"id":"46cf4818-8ce7-4644-9ee1-414e4c4960e7",
            
"name":"Danny M. Groenewegen",
            
"key":"danny-m.-groenewegen",
            
"url":"http://researchr.org/alias/danny-m.-groenewegen"
         
}
      
},
      
{
         
"person":{
            
"id":"f0fbf7c0-9729-4ec8-b3c9-5f30dbd9614b",
            
"fullname":"Eelco Visser",
            
"key":"eelcovisser",
            
"url":"http://researchr.org/profile/eelcovisser"
         
},
         
"alias":{
            
"id":"f68ba0ee-899e-4c4c-9d8a-6fed5092830a",
            
"name":"Eelco Visser",
            
"key":"eelco-visser",
            
"url":"http://researchr.org/alias/eelco-visser"
         
}
      
}
   
],
   
"title":"Code generation by model transformation: a case study in transformation modularity",
   
"month":"June",
   
"volumenumber":"9",
   
"year":"2010",
   
"note":"",
   
"key":"HemelKGV-2010",
   
"doi":"http://dx.doi.org/10.1007/s10270-009-0136-1"
}

     Here we can see the attributes of the paper like the abstract, the url of the paper, the date it was published, etc.  But what we also see is a nested array of the authors of the paper. Each author has a nested person object and alias object. So we have a fairly complex piece of JSON to work with, but not so complex that I can't clearly describe how to deal with it. 

      Our data flow for this SSIS package is going to look something like this:


Figure 1. Data Flow

We have outputs for attributes of the main paper, the person attributes of the authors as well as the attributes of the alias of the authors. So, let's drag a script component onto the design surface and begin building this solution. When prompted to select a type, pick source:


Figure 2. Script Component Type
Click on Inputs and Outputs to pull up this screen, this is where we will configure our outputs. First we build out the default output for the attributes of the paper. All the fields are strings (DT_STR) except for abstractnote and note which i made DT_TEXT, as well as lastpage and firstpage which I made integers(DT_I4):


Figure 3. Script Component Inputs and Outputs Screen(Paper)
     The output for both Author Person and Author Alias will match what is in the JSON except for 2 fields. I added parentpaperid to Author Person so we could link the author data back to the paper and I added personid to the Author Alias so I could link back the alias to the person:


Figure 4. Script Component Inputs and Outputs Screen (Authors)

From reading my previous post on this, you'll know that we will need to create the classes that this JSON feed will need to deserialize into. Luckily, I was recently shown a site that makes this very easy. When copying the JSON text to json2csharp.com,  it  produced for me these classes to derserialize my JSON into:


public class Person
{
   public string id { get; set; }
    public string fullname { get; set; }
    public string key { get; set; }
    public string url { get; set; }
}

public class Alias
{
    public string id { get; set; }
    public string name { get; set; }
    public string key { get; set; }
    public string url { get; set; }
}

public class Author
{
    public Person person { get; set; }
    public Alias alias { get; set; }
}

public class RootObject
{
    public string @abstract { get; set; }
    public int lastpage { get; set; }
    public string type { get; set; }
    public string url { get; set; }
    public int firstpage { get; set; }
    public string issuenumber { get; set; }
    public string journal { get; set; }
    public string id { get; set; }
    public List<Author> authors { get; set; }
    public string title { get; set; }
    public string month { get; set; }
    public string volumenumber { get; set; }
    public string year { get; set; }
    public string note { get; set; }
    public string key { get; set; }
    public string doi { get; set; }
}
     These classes will hold all of our data and allow it to be outputted to our 3 output buffers we created. So now go back to the script screen, click on the Edit Script button, and we can start some coding (be aware that you need to add System.Web.Extensions.dll as a reference, I wrote a step by step on how to do this in my last post regarding JSON here):

#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.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://researchr.org/api/publication/HemelKGV-2010";

        try
        {
            //Call getWebServiceResult to return our paper
            RootObject outPutResponse = GetWebServiceResult(wUrl);
            //Output main attributes of paper
            Output0Buffer.AddRow();
            if (outPutResponse.@abstract == null)
            {
                Output0Buffer.abstractnote.SetNull();
            }
            else
            {
                Output0Buffer.abstractnote.AddBlobData(Encoding.ASCII.GetBytes(outPutResponse.@abstract));
            }
            Output0Buffer.lastpage = outPutResponse.lastpage;
            Output0Buffer.type = outPutResponse.type;
            Output0Buffer.url = outPutResponse.url;
            Output0Buffer.firstpage = outPutResponse.firstpage;
            Output0Buffer.issuenumber = outPutResponse.issuenumber;
            Output0Buffer.journal = outPutResponse.journal;
            Output0Buffer.id = outPutResponse.id;
            Output0Buffer.title = outPutResponse.title;
            Output0Buffer.month = outPutResponse.month;
            Output0Buffer.volumenumber = outPutResponse.volumenumber;
            Output0Buffer.year = outPutResponse.year;
            if (outPutResponse.note == null)
            {
                Output0Buffer.note.SetNull();
            }
            else
            {
                Output0Buffer.note.AddBlobData(Encoding.ASCII.GetBytes(outPutResponse.note));
            }
            Output0Buffer.key = outPutResponse.key;
            Output0Buffer.doi = outPutResponse.doi;
            //Output author info
            foreach (Author auth in outPutResponse.authors)
            {
                //Output person info
                AuthorPersonBuffer.AddRow();
                AuthorPersonBuffer.id = auth.person.id;
                AuthorPersonBuffer.fullname = auth.person.fullname;
                AuthorPersonBuffer.key = auth.person.key;
                AuthorPersonBuffer.url = auth.person.url;
                AuthorPersonBuffer.parentpaperid = outPutResponse.id;//link person to main paper

                //Output alias info
                AuthorAliasBuffer.AddRow();
                AuthorAliasBuffer.id = auth.alias.id;
                AuthorAliasBuffer.name = auth.alias.name;
                AuthorAliasBuffer.key = auth.alias.key;
                AuthorAliasBuffer.url = auth.alias.url;
                AuthorAliasBuffer.personid = auth.person.id;//link alias to person
            }

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

    }

    /// <summary>
    /// Method to return our research paper
    /// </summary>
    /// <param name="wUrl">The web service URL to call</param>
    /// <returns>A single research paper</returns>
    private RootObject GetWebServiceResult(string wUrl)
    {

        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
        RootObject 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();
                    reader.Close();
                }

                //Deserialize our JSON
                JavaScriptSerializer sr = new JavaScriptSerializer();
                jsonResponse = sr.Deserialize<RootObject>(jsonString);

            }
            //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
}
#endregion

#region JSON Classes
//Attributes of the author
public class Person
{

    public string id { getset; }

    public string fullname { getset; }

    public string key { getset; }

    public string url { getset; }

}
//Alias of the author
public class Alias
{

    public string id { getset; }

    public string name { getset; }

    public string key { getset; }

    public string url { getset; }

}
//Authors of the paper
public class Author
{

    public Person person { getset; }

    public Alias alias { getset; }

}
//Root for Paper
public class RootObject
{

    public string @abstract { getset; }

    public int lastpage { getset; }

    public string type { getset; }

    public string url { getset; }

    public int firstpage { getset; }

    public string issuenumber { getset; }

    public string journal { getset; }

    public string id { getset; }

    public List<Author> authors { getset; }

    public string title { getset; }

    public string month { getset; }

    public string volumenumber { getset; }

    public string year { getset; }

    public string note { getset; }

    public string key { getset; }

    public string doi { getset; }

}

#endregion

     Let's step through the output and explain what's going on here. After we deserialize our JSON into our various classes jsonResponse = sr.Deserialize<RootObject>(jsonString); (keep in mind we have one paper coming back, if we had multiple you would want to change the generic to an array or list) we then output our attributes for the paper to the Ouput0Buffer:
//Call getWebServiceResult to return our paper
RootObject outPutResponse = GetWebServiceResult(wUrl);
//Output main attributes of paper
  Output0Buffer.AddRow();
  if (outPutResponse.@abstract == null)
  {
     Output0Buffer.abstractnote.SetNull();
  }
  else
  {
   Output0Buffer.abstractnote.AddBlobData(Encoding.ASCII.GetBytes(outPutResponse.@abstract));
  }
  Output0Buffer.lastpage = outPutResponse.lastpage;
 .....

After that we need to loop through the Authors list object and output our person and alias attributes, starting with person:

 foreach (Author auth in outPutResponse.authors)
            {
                //Output person info
                AuthorPersonBuffer.AddRow();
                AuthorPersonBuffer.id = auth.person.id;
                AuthorPersonBuffer.fullname = auth.person.fullname;
                AuthorPersonBuffer.key = auth.person.key;
                AuthorPersonBuffer.url = auth.person.url;
                AuthorPersonBuffer.parentpaperid = outPutResponse.id; 

These match the attributes of Person except for one attribute that I added  AuthorPersonBuffer.parentpaperid = outPutResponse.id; which is the id attribute of the paper so I can link this author to the paper. Next, we output our alias data:

                //Output alias info
                AuthorAliasBuffer.AddRow();
                AuthorAliasBuffer.id = auth.alias.id;
                AuthorAliasBuffer.name = auth.alias.name;
                AuthorAliasBuffer.key = auth.alias.key;
                AuthorAliasBuffer.url = auth.alias.url;
                AuthorAliasBuffer.personid = auth.person.id;

These match the attributes of Alias except for one attribute that I added  AuthorAliasBuffer.personid = auth.person.id; which is the id attribute of the person I can link this alias back to.

     With coding completed let's save, build and run the package. (I added data viewers to all of the outputs so we can see the results):

Figure 5. Data Viewers

     Here you can see all of the data we pulled out of that JSON in our data flow. When arriving at database table destinations you can use SQL to join the main paper to author persons on id = parentpaperid and author persons to author alias on id = personid(This is definitely not the best database design, but suits the example as a visual and is only intended for staging. You would want to apply some transformations on this data once its at the destination to tidy up the design) :


Figure 6. Destination Tables

70 comments:

  1. Your instructions are great, but I have one question. You state in the section where you explain the output that "keep in mind we have one paper coming back, if we had multiple you would want to change the generic to an array or list". I have a json output that I need to pull into SSIS that has multiple records, and has records nested within each of those records. Can you provide an example of what you mean by changing "the generic to an array or list"? I've tried to do a "For Each" loop, but I keep getting that it's not a system.array.

    ReplyDelete
    Replies
    1. Can you share a sample of your JSON? You can change any proprietary data that may be in there. If you have a lot of nesting you may need to loop through a few collections.

      Delete
    2. {"offices":[{"officeid":"ABC0001","uuid":"ABC001","territory_name":"Territory 1","territory_state":"Oregon","address1":"123 Address1 Ave","address2":"Highway 10 ","city":"Albany","state":"Oregon","zip":"97321","country":"United States","phone":"(555) 111-2222","fax":"(555) 111-2223","email":"email1@test.com","url":"mytest.com","latitude":null,"longitude":null,"is_google_map_displayed":"0","subdomain":"open","officeowners":[{"name_first":"Jane","name_last":"Doe","certifications":null}]},{"officeid":"ABC0002","uuid":"ABC0002","territory_name":"Territory2","territory_state":"Washington","address1":"1234 Address10 Ave","address2":null,"city":"Seattle","state":"Washington","zip":"99999","country":"United States","phone":"(555) 222-1111","fax":"(555) 222-1112","email":"email@mytest2.com","url":null,"latitude":null,"longitude":null,"is_google_map_displayed":"0","subdomain":null,"officeowners":[{"name_first":"John","name_last":"Doe","certifications":null}]}

      Delete
    3. I used your link you included to generate the classes (json2csharp.com)

      Delete
    4. can you send me the classes then? The JSON is not parsing correctly in Json2csharp.com/

      Delete
    5. Nevermind I figured it out. You left off the last

      ]
      }

      Delete
    6. Ok I figured this out.

      1. First thing I did was create 2 outputs on the inputs and outputs screen. The first called Office the other called OfficeOwner.

      2. Next make sure your JSON classes look like this. It was changing your nulls to objects and am pretty sure they are strings:

      #region JSON Classes

      public class Officeowner
      {
      public string name_first { get; set; }
      public string name_last { get; set; }
      public string certifications { get; set; }
      }

      public class Office
      {
      public string officeid { get; set; }
      public string uuid { get; set; }
      public string territory_name { get; set; }
      public string territory_state { get; set; }
      public string address1 { get; set; }
      public string address2 { get; set; }
      public string city { get; set; }
      public string state { get; set; }
      public string zip { get; set; }
      public string country { get; set; }
      public string phone { get; set; }
      public string fax { get; set; }
      public string email { get; set; }
      public string url { get; set; }
      public string latitude { get; set; }
      public string longitude { get; set; }
      public string is_google_map_displayed { get; set; }
      public string subdomain { get; set; }
      public List officeowners { get; set; }
      }

      public class RootObject
      {
      public List offices { get; set; }
      }

      #endregion

      3. You only need to change the code for the overriden CreateNewOutputRows method to this:

      public override void CreateNewOutputRows()
      {

      //Set Webservice URL
      string wUrl = "your url goes here";

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

      foreach(Office office in outPutResponse.offices)
      {

      //Output main attributes of office
      OfficeBuffer.AddRow();

      OfficeBuffer.officeid = office.officeid;
      OfficeBuffer.uuid = office.uuid;
      OfficeBuffer.territoryname = office.territory_name;
      OfficeBuffer.territorystate = office.territory_state;
      OfficeBuffer.address1 = office.address1;
      OfficeBuffer.address2 = office.address2;
      OfficeBuffer.city = office.city;
      OfficeBuffer.state = office.state;
      OfficeBuffer.zip = office.zip;
      OfficeBuffer.country = office.country;
      OfficeBuffer.phone = office.phone;
      OfficeBuffer.fax = office.fax;
      OfficeBuffer.email = office.email;
      OfficeBuffer.url = office.url;
      OfficeBuffer.latitude = office.latitude;
      OfficeBuffer.longitude = office.longitude;
      OfficeBuffer.isgooglemapdisplayed = office.is_google_map_displayed;
      OfficeBuffer.subdomain = office.subdomain;

      //Output office owner info
      foreach (Officeowner owner in office.officeowners)
      {
      //Output office owner info
      OfficeOwnerBuffer.AddRow();

      OfficeOwnerBuffer.namefirst = owner.name_first;
      OfficeOwnerBuffer.namelast = owner.name_last;
      OfficeOwnerBuffer.certifications = owner.certifications;
      OfficeOwnerBuffer.officeid = office.officeid;
      }

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

      }

      This should get you your output. You can tie the Office output to the OfficeOwner output on officeid. All we had to do was use the same methodology used in this post but just add another nested loop in it. Let me know if you understand and get this working.

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

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

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

      Delete
    10. You're the best - I think my problem now is more of a conversion issue from c# to vb.net as I'm now getting a "too few type arguments to 'system.collections.generic.list(of t)' on the list items in the classes. I'm researching that now.

      Delete
    11. And I got it - thank you so much!!!!

      Delete
    12. NP good luck with your project :)

      Delete
  2. Hi Jim,

    Great series of articles on SSIS and JSON. Hopefully, you're still monitoring comments on this article as I'm hoping you can help me with a problem. I have JSON data that has a list of items ("items") nested within a top level element ("data") and the "data" element does not have any of its own fields. Here is a sample of the JSON and the classes generated by json2csharp.com:

    {
    "data": {
    "items": [
    {
    "updated_at": 1411245491,
    "name": "12Return",
    "path": "organization/12return",
    "created_at": 1411244334,
    "type": "Organization"
    },
    {
    "updated_at": 1411243797,
    "name": "gangboy jeans",
    "path": "organization/gangboy-jeans",
    "created_at": 1411243571,
    "type": "Organization"
    }
    ],
    "paging": {
    "next_page_url": "http://api.crunchbase.com/v/2/organizations?page=3",
    "total_items": 287283,
    "number_of_pages": 288,
    "sort_order": "created_at DESC",
    "items_per_page": 1000,
    "prev_page_url": "http://api.crunchbase.com/v/2/organizations?page=1",
    "current_page": 2
    }
    },
    "metadata": {
    "image_path_prefix": "http://images.crunchbase.com/",
    "www_path_prefix": "http://www.crunchbase.com/",
    "api_path_prefix": "http://api.crunchbase.com/v/2/",
    "version": 2
    }
    }

    public class Item
    {
    public int updated_at { get; set; }
    public string name { get; set; }
    public string path { get; set; }
    public int created_at { get; set; }
    public string type { get; set; }
    }

    public class Paging
    {
    public string next_page_url { get; set; }
    public int total_items { get; set; }
    public int number_of_pages { get; set; }
    public string sort_order { get; set; }
    public int items_per_page { get; set; }
    public string prev_page_url { get; set; }
    public int current_page { get; set; }
    }

    public class Data
    {
    public List items { get; set; }
    public Paging paging { get; set; }
    }

    public class Metadata
    {
    public string image_path_prefix { get; set; }
    public string www_path_prefix { get; set; }
    public string api_path_prefix { get; set; }
    public int version { get; set; }
    }

    public class RootObject
    {
    public Data data { get; set; }
    public Metadata metadata { get; set; }
    }

    Do I need to setup an output for the "data" element in order to get to the "items"? If so, how do you do this without there being output fields (SSIS throws an error without any output fields)? I tried going straight to the "items" using

    foreach (Item it in outPutResponse.items)

    but the "items" collection is null at the point the code tries to access it. Do I need to load "data" into the buffer before moving to the "items"?

    Hopefully, my questions make sense as I'm more of a hack than a professional developer so some of my terminology might not be accurate. Let me know if you need any additional information. I feel like I'm right there and just need a little push to get me over the hump. Thanks for your posts as they've gotten me way further than I would have otherwise.

    ReplyDelete
    Replies
    1. Try replacing with this code:

      foreach (Item it in outPutResponse.data.items)

      Delete
  3. Can you give an example of when there are multiple papers coming back? I don't quite understand what the code needs to be changed to from your comment:

    " if we had multiple you would want to change the generic to an array or list"

    ReplyDelete
    Replies
    1. Thats if you had multiple documents coming back. You could changed your root object to a collection. Or..which is what json2csharp.com would do is have a root object and have the collection be a member of that like in this post http://dennysjymbo.blogspot.com/2014/03/utilizing-net-40-datacontractjsonserial.html. Do you need help with some code?

      Delete
  4. How would you handle an output type of public List string "sample field name" within a class?

    ReplyDelete
    Replies
    1. In this post I have an example of a list within a class. I guess Im not understanding your question? Do you have sample JSON to share?

      Delete
  5. Thanks very much for the quick response! Below is a sample of JSON that generates the output I referred to earlier.

    I generated the classes from the json2csharp site as you recommended. From your instructions, I'm clear on how to handle List Product products in the RootObject class. It is List string devices in the Product class I'm struggling with.

    {
    "page_index": 0,
    "code": 200,
    "products": [
    {
    "status": true,
    "product_id": 9099999999,
    "devices": [
    "sample device"
    ],
    "last_sales_date": "2999-01-01",
    "first_sales_date": "2999-01-01",
    "product_name": "sample product name",
    "icon": "sample icon"
    }
    ],
    "prev_page": null,
    "page_num": 1,
    "next_page": null
    }

    ReplyDelete
    Replies
    1. Ok here's how I got this to work. On the Inputs and Outputs screen I created 3 outputs:

      RootObject
      pageindex DT_I4
      code DT_I4
      prevpage DT_I4
      pagenum DT_I4
      nextpage DT_I4

      Devices
      productid DT_I8
      device string

      Product
      status DT_BOOL
      productid DT_I8
      lastsalesdate DT_DBDATE
      firstsalesdate DT_DBDATE
      productname DT_STR 75
      icon DT_STR 75
      code DT_I4

      The you can join RootObject to Product on code and Product to Devices on productid

      The C# code is as follows:

      Delete
    2. #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.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
      public override void CreateNewOutputRows()
      {

      //Set Webservice URL
      string wUrl = "YOUR URL GOES HERE";

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

      //Output main attributes of root object
      RootObjectBuffer.AddRow();
      RootObjectBuffer.pageindex=outPutResponse.page_index;
      RootObjectBuffer.code=outPutResponse.code;
      if (outPutResponse.prev_page != null)
      {
      RootObjectBuffer.prevpage = outPutResponse.prev_page.GetValueOrDefault(); ;
      }

      RootObjectBuffer.pagenum=outPutResponse.page_num;

      if (outPutResponse.next_page != null)
      {
      RootObjectBuffer.nextpage = outPutResponse.next_page.GetValueOrDefault(); ;
      }

      //Output products
      foreach (Product prod in outPutResponse.products)
      {
      //Output product info
      ProductBuffer.AddRow();
      ProductBuffer.status = prod.status;
      ProductBuffer.productid = prod.product_id;
      ProductBuffer.code=outPutResponse.code; //link to root object
      ProductBuffer.firstsalesdate = prod.first_sales_date;
      ProductBuffer.lastsalesdate=prod.last_sales_date;
      ProductBuffer.productname=prod.product_name;
      ProductBuffer.icon=prod.icon;

      //Output devices
      foreach(string device in prod.devices)
      {

      DevicesBuffer.AddRow();
      DevicesBuffer.device=device;
      DevicesBuffer.productid=prod.product_id;//link to product
      }
      }

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

      }

      Delete
    3. /// < summary >
      /// Method to return our research paper
      ///
      /// < param name="wUrl" >The web service URL to call< /param >
      /// < returns >A single research paper
      private RootObject GetWebServiceResult(string wUrl)
      {

      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      RootObject 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();
      reader.Close();
      }

      //Deserialize our JSON
      JavaScriptSerializer sr = new JavaScriptSerializer();
      jsonResponse = sr.Deserialize< RootObject >(jsonString);

      }
      //Output connection error message
      else
      {
      FailComponent(httpWResp.StatusCode.ToString());

      }
      }
      //Output JSON parsing error
      catch (Exception e)
      {
      FailComponent(e.ToString());
      }
      return jsonResponse;

      }

      Delete
    4. /// < summary >
      /// Outputs error message
      ///
      /// < param name="errorMsg" >Full error text
      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

      public class Product
      {
      public bool status { get; set; }
      public long product_id { get; set; }
      public List< string > devices { get; set; }
      public DateTime last_sales_date { get; set; }
      public DateTime first_sales_date { get; set; }
      public string product_name { get; set; }
      public string icon { get; set; }
      }

      public class RootObject
      {
      public int page_index { get; set; }
      public int code { get; set; }
      public List< Product > products { get; set; }
      public int? prev_page { get; set; }
      public int page_num { get; set; }
      public int? next_page { get; set; }
      }

      }

      #endregion

      Delete
    5. Sorry had to split up the code, there is a max char accepted into these text boxes...

      Delete
    6. Hi Jim, My json is very similar to this, but instead of RootObject, I have to use an array RootObject[], but when I do this , I get System.Array does not contain a definition error, can you please direct me

      Delete
  6. This worked great. You are seriously awesome! Thanks again for your help. I really appreciate it. :)

    ReplyDelete
  7. Hi Jim,

    I'm back with another question. I've been looking around trying to find an answer to this online but since my c# knowledge is pretty limited, I'm struggling with how to handle this new challenge. I've run into a situation where I have a JSON response that isn't consistent. Most times I will get the below response format:

    {"ratings": [{"country": null, "current_ratings": null, "all_ratings": {"star_1_count": 2, "average": 2.1, "star_5_count": 222, "rating_count": 222, "star_2_count": 22, "star_4_count": 22, "star_3_count": 222}}], "page_index": 0, "code": 200, "prev_page": null, "next_page": null, "page_num": 1, "product_name": "Sample Product Name"}

    But there are occasions where I'm getting this response format:
    {"ratings": [], "page_index": 0, "code": 200, "prev_page": null, "next_page": null, "page_num": 1, "product_name": "Example Product Name"}

    These responses generate two different sets of classes but with the same "name". My pull fails every time on the response above because I'm not sure how to code for it.

    Do you have any ideas on how to approach this? Thanks!


    ReplyDelete
    Replies
    1. Can you share the error message you're getting?

      Delete
  8. Thanks again for the quick response, Jim! I’m going to post my Outputs, the code I’m using in my script component and the error messages I get back.

    Outputs:

    Root Object
    PageIndex DT_I4
    Code DT_I4
    PrevPage DT_I4
    NextPage DT_I4
    PageNum DT_I4
    ProductID DT_STR
    ProductName DT_STR

    Rating
    ProductID DT_STR
    Country DT_STR

    Current Rating and All Ratings
    ProductID DT_STR
    Average DT_DECIMAL
    Ratingcount DT_I4
    Star1Count DT_I4
    Star2Count DT_I4
    Star3Count DT_I4
    Star4Count DT_I4
    Star5Count DT_I4

    ReplyDelete
  9. Script component:

    #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;
    using System.Windows.Forms;
    using System.Web.Script.Serialization;

    #endregion

    #region Class


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

    /// Outputs records to the output buffer
    ///
    public override void CreateNewOutputRows()
    {
    string vProductID = Variables.ProductID;
    string vMarket = Variables.Market;
    string vEndDate = Variables.EndDate;
    string vBeginDate = Variables.BeginDate;
    string wUrl = “The url I’m hitting”
    //MessageBox.Show(wUrl);

    try
    {
    RootObject outPutResponse = GetWebServiceResult(wUrl);
    //Output main attributes of root object
    RootObjectBuffer.AddRow();
    RootObjectBuffer.PageIndex = outPutResponse.page_index;
    RootObjectBuffer.Code = outPutResponse.code;
    RootObjectBuffer.ProductID = vProductID;
    RootObjectBuffer.ProductName = outPutResponse.product_name;
    if (outPutResponse.prev_page != null)
    {
    RootObjectBuffer.PrevPage = outPutResponse.prev_page.GetValueOrDefault(); ;
    }

    RootObjectBuffer.PageNum = outPutResponse.page_num;

    if (outPutResponse.next_page != null)
    {
    RootObjectBuffer.NextPage = outPutResponse.next_page.GetValueOrDefault(); ;
    }

    if (outPutResponse.ratings == null)
    {
    MessageBox.Show("Rating List is Null");
    }

    else
    //If we get data back
    {
    foreach (Rating rt in outPutResponse.ratings)
    {

    //Rating
    if (rt.country == null)
    {
    RatingBuffer.AddRow();
    RatingBuffer.ProductID = vProductID;
    RatingBuffer.Country_IsNull = true;

    }

    else
    {
    RatingBuffer.AddRow();
    RatingBuffer.ProductID = vProductID;
    RatingBuffer.Country = rt.country;
    }




    ReplyDelete
  10. //CurrentRatings
    if (rt.current_ratings == null)
    {
    CurrentRatingsBuffer.AddRow();
    CurrentRatingsBuffer.ProductID = vProductID;
    CurrentRatingsBuffer.Average_IsNull = true;
    CurrentRatingsBuffer.RatingCount_IsNull = true;
    CurrentRatingsBuffer.Star1Count_IsNull = true;
    CurrentRatingsBuffer.Star2Count_IsNull = true;
    CurrentRatingsBuffer.Star3Count_IsNull = true;
    CurrentRatingsBuffer.Star4Count_IsNull = true;
    CurrentRatingsBuffer.Star5Count_IsNull = true;
    //Console.WriteLine("Found Null Array");
    }

    else
    {
    CurrentRatingsBuffer.AddRow();
    CurrentRatingsBuffer.ProductID = vProductID;
    CurrentRatingsBuffer.Average = rt.current_ratings.average;
    CurrentRatingsBuffer.RatingCount = rt.current_ratings.rating_count;
    CurrentRatingsBuffer.Star1Count = rt.current_ratings.star_1_count;
    CurrentRatingsBuffer.Star2Count = rt.current_ratings.star_2_count;
    CurrentRatingsBuffer.Star3Count = rt.current_ratings.star_3_count;
    CurrentRatingsBuffer.Star4Count = rt.current_ratings.star_4_count;
    CurrentRatingsBuffer.Star5Count = rt.current_ratings.star_5_count;
    }

    //AllRatings
    if (rt.all_ratings == null)
    {
    AllRatingsBuffer.AddRow();
    AllRatingsBuffer.ProductID = vProductID;
    AllRatingsBuffer.Average_IsNull = true;
    AllRatingsBuffer.RatingCount_IsNull = true;
    AllRatingsBuffer.Star1Count_IsNull = true;
    AllRatingsBuffer.Star2Count_IsNull = true;
    AllRatingsBuffer.Star3Count_IsNull = true;
    AllRatingsBuffer.Star4Count_IsNull = true;
    AllRatingsBuffer.Star5Count_IsNull = true;
    //Console.WriteLine("Found Null Array");
    }

    else
    {
    AllRatingsBuffer.AddRow();
    AllRatingsBuffer.ProductID = vProductID;
    AllRatingsBuffer.Average = rt.all_ratings.average;
    AllRatingsBuffer.RatingCount = rt.all_ratings.rating_count;
    AllRatingsBuffer.Star1Count = rt.all_ratings.star_1_count;
    AllRatingsBuffer.Star2Count = rt.all_ratings.star_2_count;
    AllRatingsBuffer.Star3Count = rt.all_ratings.star_3_count;
    AllRatingsBuffer.Star4Count = rt.all_ratings.star_4_count;
    AllRatingsBuffer.Star5Count = rt.all_ratings.star_5_count;
    }
    }

    }
    }
    catch (Exception e)
    {

    FailComponent(e.ToString());
    }
    }

    private RootObject GetWebServiceResult(string wUrl)
    {
    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    httpWReq.Method = "GET";
    httpWReq.Headers.Add("Authorization", authToken);
    httpWReq.ContentType = "application/json";
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    RootObject jsonResponse = null;
    try
    {

    if (httpWResp.StatusCode == HttpStatusCode.OK)

    ReplyDelete
  11. {

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

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

    //MessageBox.Show(jsonString);

    //Deserialize our JSON
    JavaScriptSerializer sr = new JavaScriptSerializer();
    jsonResponse = sr.Deserialize(jsonString);
    }
    //Output connection error message
    else
    {
    FailComponent(httpWResp.StatusCode.ToString());

    }
    }
    //Output JSON parsing error

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

    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

    ReplyDelete
  12. #region JSON Classes
    public class CurrentRatings
    {
    public int star_1_count { get; set; }
    public decimal average { get; set; }
    public int star_5_count { get; set; }
    public int rating_count { get; set; }
    public int star_2_count { get; set; }
    public int star_4_count { get; set; }
    public int star_3_count { get; set; }
    }

    public class AllRatings
    {
    public int star_1_count { get; set; }
    public decimal average { get; set; }
    public int star_5_count { get; set; }
    public int rating_count { get; set; }
    public int star_2_count { get; set; }
    public int star_4_count { get; set; }
    public int star_3_count { get; set; }
    }

    public class Rating
    {
    public string country { get; set; }
    public CurrentRatings current_ratings { get; set; }
    public AllRatings all_ratings { get; set; }
    }

    public class RootObject
    {
    public List ratings { get; set; }
    public int page_index { get; set; }
    public int code { get; set; }
    public int? prev_page { get; set; }
    public int? next_page { get; set; }
    public int page_num { get; set; }
    public string product_name { get; set; }
    }
    #endregion


    ReplyDelete
  13. Error Messages:
    1)
    [Error Getting Data From Webservice!] Error: System.InvalidOperationException: Cannot convert null to a value type.
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeMain(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.AssignToPropertyOrField(Object propertyValue, Object o, String memberName, JavaScriptSerializer serializer, Boolean throwOnError)
    at System.Web.Script.Serialization.ObjectConverter.ConvertDictionaryToObject(IDictionary`2 dictionary, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeInternal(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeMain(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.AssignToPropertyOrField(Object propertyValue, Object o, String memberName, JavaScriptSerializer serializer, Boolean throwOnError)
    at System.Web.Script.Serialization.ObjectConverter.ConvertDictionaryToObject(IDictionary`2 dictionary, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeInternal(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeMain(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.AddItemToList(IList oldList, IList newList, Type elementType, JavaScriptSerializer serializer, Boolean throwOnError)
    at System.Web.Script.Serialization.ObjectConverter.ConvertListToObject(IList list, Type type, JavaScriptSerializer serializer, Boolean throwOnError, IList& convertedList)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeInternal(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeMain(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.AssignToPropertyOrField(Object propertyValue, Object o, String memberName, JavaScriptSerializer serializer, Boolean throwOnError)
    at System.Web.Script.Serialization.ObjectConverter.ConvertDictionaryToObject(IDictionary`2 dictionary, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeInternal(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.ObjectConverter.ConvertObjectToTypeMain(Object o, Type type, JavaScriptSerializer serializer, Boolean throwOnError, Object& convertedObject)
    at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize[T](String input)
    at ScriptMain.GetWebServiceResult(String wUrl)


    2)
    [Error Getting Data From Webservice!] Error: System.NullReferenceException: Object reference not set to an instance of an object.
    at ScriptMain.CreateNewOutputRows()

    ReplyDelete
    Replies
    1. Ok this worked for me. I created 2 outputs.

      RootObject
      PageIndex DT_I4
      Code DT_I4
      prevpage DT_I4
      nextpage DT_I4
      pagenum DT_I4
      productname DT_STR 75
      productid DT_STR 50

      Rating
      allstar1count DT_I4
      allaverage DT_DECIMAL
      allstar5count DT_I4
      allratingcount DT_I4
      allstar2count DT_I4
      allstar4count DT_I4
      allstar3count DT_I4
      country DT_STR 50
      currentstar1count DT_I4
      currentaverage DT_DECIMAL
      currentstar5count DT_I4
      currentratingcount DT_I4
      currentstar2count DT_I4
      currentstar4count DT_I4
      currentstar3count DT_I4
      productid DT_STR 50

      I assume the unique key for rating will be a combo of Productid and country.






      Delete
    2. #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.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()
      {

      string vProductID = Variables.ProductID;
      string vMarket = Variables.Market;
      string vEndDate = Variables.EndDate;
      string vBeginDate = Variables.BeginDate;
      string wUrl = Variables.wUrl;

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

      //Output main attributes of root object
      RootObjectBuffer.AddRow();
      RootObjectBuffer.pageindex=outPutResponse.page_index;
      RootObjectBuffer.code=outPutResponse.code;
      if (outPutResponse.prev_page != null)
      {
      RootObjectBuffer.prevpage = outPutResponse.prev_page.GetValueOrDefault();
      }

      RootObjectBuffer.pagenum=outPutResponse.page_num;

      if (outPutResponse.next_page != null)
      {
      RootObjectBuffer.nextpage = outPutResponse.next_page.GetValueOrDefault();
      }
      RootObjectBuffer.productname = outPutResponse.product_name;
      RootObjectBuffer.productid = vProductID;

      Delete
    3. //Output rating
      foreach (Rating rate in outPutResponse.ratings)
      {

      RatingBuffer.AddRow();
      RatingBuffer.country = rate.country;
      RatingBuffer.productid = vProductID;//link to root object

      if (rate.all_ratings != null)
      {
      RatingBuffer.allstar1count = rate.all_ratings.star_1_count;
      RatingBuffer.allaverage = rate.all_ratings.average;
      RatingBuffer.allstar5count = rate.all_ratings.star_5_count;
      RatingBuffer.allratingcount = rate.all_ratings.rating_count;
      RatingBuffer.allstar2count = rate.all_ratings.star_2_count;
      RatingBuffer.allstar4count = rate.all_ratings.star_4_count;
      RatingBuffer.allstar3count = rate.all_ratings.star_3_count;
      }
      else
      {
      RatingBuffer.allstar1count_IsNull=true;
      RatingBuffer.allaverage_IsNull = true;
      RatingBuffer.allstar5count_IsNull = true;
      RatingBuffer.allratingcount_IsNull = true;
      RatingBuffer.allstar2count_IsNull = true;
      RatingBuffer.allstar4count_IsNull = true;
      RatingBuffer.allstar3count_IsNull = true;
      }

      if (rate.current_ratings != null)
      {
      RatingBuffer.currentstar1count = rate.current_ratings.star_1_count;
      RatingBuffer.currentaverage = rate.current_ratings.average;
      RatingBuffer.currentstar5count = rate.current_ratings.star_5_count;
      RatingBuffer.currentratingcount = rate.current_ratings.rating_count;
      RatingBuffer.currentstar2count = rate.current_ratings.star_2_count;
      RatingBuffer.currentstar4count = rate.current_ratings.star_4_count;
      RatingBuffer.currentstar3count = rate.current_ratings.star_3_count;
      }
      else
      {
      RatingBuffer.currentstar1count_IsNull = true;
      RatingBuffer.currentaverage_IsNull = true;
      RatingBuffer.currentstar5count_IsNull = true;
      RatingBuffer.currentratingcount_IsNull = true;
      RatingBuffer.currentstar2count_IsNull = true;
      RatingBuffer.currentstar4count_IsNull = true;
      RatingBuffer.currentstar3count_IsNull = true;


      }
      }

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

      }

      Delete
    4. private RootObject GetWebServiceResult(string wUrl)
      {
      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      httpWReq.Method = "GET";
      httpWReq.Headers.Add("Authorization", authToken);
      httpWReq.ContentType = "application/json";
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      RootObject jsonResponse = null;
      try
      {

      if (httpWResp.StatusCode == HttpStatusCode.OK)

      {

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

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

      //MessageBox.Show(jsonString);

      //Deserialize our JSON
      JavaScriptSerializer sr = new JavaScriptSerializer();
      jsonResponse = sr.Deserialize(jsonString);
      }
      //Output connection error message
      else
      {
      FailComponent(httpWResp.StatusCode.ToString());

      }
      }
      //Output JSON parsing error

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

      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

      Delete
    5. #region JSON Classes

      public class AllRatings
      {
      public int star_1_count { get; set; }
      public decimal average { get; set; }
      public int star_5_count { get; set; }
      public int rating_count { get; set; }
      public int star_2_count { get; set; }
      public int star_4_count { get; set; }
      public int star_3_count { get; set; }
      }

      public class Rating
      {
      public string country { get; set; }
      public CurrentRatings current_ratings { get; set; }
      public AllRatings all_ratings { get; set; }
      }

      public class CurrentRatings
      {
      public int star_1_count { get; set; }
      public decimal average { get; set; }
      public int star_5_count { get; set; }
      public int rating_count { get; set; }
      public int star_2_count { get; set; }
      public int star_4_count { get; set; }
      public int star_3_count { get; set; }
      }

      public class RootObject
      {
      public List< Rating > ratings { get; set; }
      public int page_index { get; set; }
      public int code { get; set; }
      public int? prev_page { get; set; }
      public int? next_page { get; set; }
      public int page_num { get; set; }
      public string product_name { get; set; }
      }



      #endregion

      Delete
  14. Thanks Jim! I made adjustments to my outputs and script components to match yours but still got both errors. I ended up making a change to my classes. I changed any int to int? and then changed how I set my outputs (see below) and now it works successfully.

    if (rate.all_ratings != null)
    {
    RatingBuffer.AllAverage = rate.all_ratings.average ?? 0;
    RatingBuffer.AllStar5Count = rate.all_ratings.star_5_count ?? 0;
    RatingBuffer.AllRatingCount = rate.all_ratings.rating_count ?? 0;
    RatingBuffer.AllStar1Count = rate.all_ratings.star_1_count ?? 0;
    RatingBuffer.AllStar2Count = rate.all_ratings.star_2_count ?? 0;
    RatingBuffer.AllStar4Count = rate.all_ratings.star_4_count ?? 0;
    RatingBuffer.AllStar3Count = rate.all_ratings.star_3_count ?? 0;
    }
    else
    {
    RatingBuffer.AllAverage = 0;
    RatingBuffer.AllStar5Count = 0;
    RatingBuffer.AllRatingCount = 0;
    RatingBuffer.AllStar1Count = 0;
    RatingBuffer.AllStar2Count = 0;
    RatingBuffer.AllStar4Count = 0;
    RatingBuffer.AllStar3Count = 0;
    }

    ReplyDelete
  15. Hey Jim! This entire article series is great. I'm pretty new with C# and very new with JSON, but this has helped me get my brain around the process. I haven't gotten live access to the API yet, but I know there will be one new wrinkle... I have to loop through a set of missions one at a time and each mission will have a set of tasks.

    The test API is http://private-anon-3441cdbce-gsc.apiary-mock.com/external/v1/missions/{1}?include=tasks. It returns the JSON of

    {
    "request"=>{
    "status_code"=>200,
    "status_message"=>"OK",
    "path"=>"/external/v1/missions/1",
    "method"=>"GET",
    "params"=>{
    "include"=>"tasks"
    }
    },
    "data"=>{
    "archived_at"=>nil,
    "completed_at"=>nil,
    "created_at"=>"2014-05-13T15:11:27-04:00",
    "id"=>1,
    "image_url"=>nil,
    "instructions"=>"How are they performing?",
    "maximum_responses_per_location"=>nil,
    "maximum_responses_per_user"=>nil,
    "maximum_responses_per_user_per_location"=>nil,
    "name"=>"Marketing 1/2013",
    "state"=>"draft",
    "updated_at"=>"2014-05-13T15:11:27-04:00",
    "tasks"=>[
    {
    "campaign_id"=>1,
    "configuration"=>{},
    "description"=>"Describe it",
    "id"=>12,
    "optional"=>false,
    "parent_task_id"=>nil,
    "parent_task_value"=>nil,
    "position"=>2,
    "section_id"=>1,
    "title"=>"How many eggs did you find?"
    },
    ...
    ]
    }
    }

    Do you know of a good way to loop through the mission ID and extract the data for each mission and the multiple tasks?

    ReplyDelete
  16. hi is this is still active? tried to follow this but i encountered proxy required error 407 when calling to webservice

    ReplyDelete
    Replies
    1. Can you share some code? Do you have documentation for the web service API?

      Delete
  17. Hi Jim

    Thank you very much for sharing this useful information. We could learn more from people of your skills.
    Thanks

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete
  19. Hi Jim, are you still monitoring the comments on this page, coz I have a need to convert a JSON string from a webservice to be converted into a format which I can insert into a SQL Server. Please let me know your email and I can email you the JSON text and what issues I am facing..

    ReplyDelete
  20. Hi Jim, do you have VB version of this? I don't know C#. Thanks!!

    ReplyDelete
  21. Hi JIm, Are you still monitorin the comments, I need your help with jason extract to sql server

    ReplyDelete
    Replies
    1. I have sent you an email, if you please help me out that would be great

      Delete
    2. What help do u need? What have you tried?

      Delete
    3. [{"name":"2016_07","commit":{"id":"07639d037833c0abcf22a1cd5460b136db5eeea0","message":"ALM9999,Merged revision(s) 11694-11699 from branches/2016_07/DEVELOPMENT:\n","parent_ids":["744278e57b2dcd1bcb8d9b8250ea77bf47dfa51f"],"authored_date":"2016-07-19T20:09:17.000+00:00","author_name":"Buzila, Mihai","author_email":"mbuzila@ifdsgroup.com","committed_date":"2016-07-19T20:09:17.000+00:00","committer_name":"Buzila, Mihai","committer_email":"mbuzila@ifdsgroup.com"},"merged":false,"protected":false,"developers_can_push":false,"developers_can_merge":false},{"name":"2016_09_RELEASE","commit":{"id":"b17b1abebb81ef77f7350431cd4b745005a02fcd","message":"added 2016_O9_RELEASE\n","parent_ids":["84d812739d85259dd212c31942512c8f43e2e023"],"authored_date":"2016-08-24T13:16:36.000-04:00","author_name":"tony","author_email":"tony@localhost.localdomain","committed_date":"2016-08-24T13:16:36.000-04:00","committer_name":"tony","committer_email":"tony@localhost.localdomain"},"merged":false,"protected":false,"developers_can_push":false,"developers_can_merge":false},{"name":"2016_11_RELEASE","commit":{"id":"7ff12b461f9a4b770c3dac8dd9eb1af3464c0896","message":"line feeds\n","parent_ids":["a268c710b07691b0ac23c195d30262cf163e3bb1"],"authored_date":"2016-11-17T15:47:30.000-05:00","author_name":"tony","author_email":"tony@localhost.localdomain","committed_date":"2016-11-17T15:47:30.000-05:00","committer_name":"tony","committer_email":"tony@localhost.localdomain"},"merged":false,"protected":false,"developers_can_push":false,"developers_can_merge":false},{"name":"2017_FINAL_BAU","commit":{"id":"fa1a6d8ad55638900a69856236dfe0a27b3b95fe","message":"added 2017_FINAL_BAU branch\n","parent_ids":["2fe4ba7d845420e10b4ed7400dcb02e3594fda0b"],"authored_date":"2017-01-16T15:08:40.000-05:00","author_name":"tony","author_email":"tony@localhost.localdomain","committed_date":"2017-01-16T15:08:40.000-05:00","committer_name":"tony","committer_email":"tony@localhost.localdomain"},"merged":false,"protected":false,"developers_can_push":false,"developers_can_merge":false}]

      Delete
    4. Hi Jim, when I generate the classes usin jasonsharp, i get two classes, I create two foreach loops, but my second foreach dosent seem to work, can you please direct me on what has to be done?

      Delete
  22. 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.Collections.Generic;
    using System.Text;
    #endregion

    ///
    /// This is the class to which to add your code. Do not change the name, attributes, or parent
    /// of this class.
    ///
    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {


    public override void CreateNewOutputRows()
    {
    /*
    Add rows by calling the AddRow method on the member variable named "Buffer".
    For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
    */

    //Set Webservice URL
    string wUrl = "http://gitrepop1/api/v3/projects/iFast-webplus%2Fwebplus/repository/branches";

    try
    {
    //Call getWebServiceResult to return our paper
    RootObject[] outPutResponse = GetWebServiceResult(wUrl);
    //Output main attributes of RootObject
    foreach(var response in outPutResponse)
    {
    Output0Buffer.AddRow();
    Output0Buffer.name = response.name;
    Output0Buffer.commit=response.commit;
    Output0Buffer.merged=response.merged;
    Output0Buffer.@protected=response.@protected;
    Output0Buffer.developers_can_push=response.developers_can_push;
    Output0Buffer.developers_can_merge = response.developers_can_merge;

    }



    foreach (Commit response in outPutResponse)
    {
    Output0Buffer.AddRow();
    Output0Buffer.id = response.id;
    Output0Buffer.message = response.message;
    Output0Buffer.parent_ids = response.parent_ids;
    Output0Buffer.author_email = response.author_email;
    Output0Buffer.committed_date = response.committed_date;
    Output0Buffer.committer_name = response.committer_name;
    Output0Buffer.committer_email = response.committer_email;
    }


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

    private RootObject[] GetWebServiceResult(string wUrl)
    {

    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    RootObject[] 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();
    reader.Close();
    }

    //Deserialize our JSON
    JavaScriptSerializer sr = new JavaScriptSerializer();
    jsonResponse = sr.Deserialize(jsonString.Trim('"'));

    }
    //Output connection error message
    else
    {
    FailComponent(httpWResp.StatusCode.ToString());

    }
    }

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. This comment has been removed by the author.

    ReplyDelete
  25. public class Field
    {
    public string Field1 { get; set; }


    }

    public class Class2
    {
    public List Fields { get; set; }

    }

    public class RootObject
    {
    public List Cls2 { get; set; }
    }

    I am getting this exception while using JavaScriptSerializer.deserialize() method

    System.NullReferenceException: Object reference not set to an instance of an object.at ScriptMain

    ReplyDelete
    Replies
    1. This problem occurs when there are more then 2 classes for 2 its working fine, can someone help what needs to be done here to resolve this error. Basically Serialization fails.

      Delete
    2. Can you share all of your code? Do you have a sample of the JSON that makes this fail?

      Delete
  26. Hi Jim,
    my json has $ in front of fields when I created my classes I removed them but I am not returning any data and I know there is some. I can return the info from the root object here is what me json looks like
    {"status": "ok",
    "computed_at": "2019-04-02T13:25:50.203850+00:00",

    "results": [
    {"$distinct_id": "fdfdfdgfd
    "$properties":
    {"$ios_app_version": "test
    "$country_code": "test",
    "$lastname": "test",
    "$region": "test",
    "$name": "test",
    "$firstname": "test",
    "$email": "test",
    "$ios_lib_version": "test",
    "$ios_device_model": "test",
    "$city": "test",
    "$ios_version": "test",
    "$last_seen": "test",
    "$ios_app_release": "test",
    "$timezone": "test"}},

    ],
    "page_size": 1000,
    "session_id": "test",
    "total": 468,
    "page": 0}

    ReplyDelete
    Replies
    1. Just go this to work it was the $ replacing them in the worked
      using (StreamReader reader = new StreamReader(responseStream))
      {
      jsonString = reader.ReadToEnd();
      jsonString = jsonString.Replace("$", "");
      reader.Close();
      }

      Delete
  27. Hello Jim I have following json file structure

    Is there an email I can send you the screenshot of the json tree structure. I am not able to find a way to upload images?

    Thanks for your help.


    ReplyDelete
  28. This comment has been removed by the author.

    ReplyDelete
  29. This comment has been removed by the author.

    ReplyDelete
  30. Thanks for sharing this useful information. JSON Feed with Nested Arrays in SSIS coding would be helpful

    ReplyDelete
  31. Hey Jim, I have following json file structure.

    {"export":{"created_at":"2022-02-23","url":"https://s3.amazonaws.com/cdn...."}}

    Could you please help me to make this run?

    ReplyDelete