Monday, May 20, 2013

Using a JSON Feed as a Data Source in SSIS

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

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

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

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

Figure 2. SSIS Variables

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


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

Figure 3. Script Component Type
On the Script screen we need to select the SSIS variables we want to use in our code:
Figure 4. Script Component Script Screen

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

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


Figure 6. Project Menu

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


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


Figure 8. Project References

Now we can start some coding:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Net;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Web.Script.Serialization;
#endregion

#region Class
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    /// <summary>Outputs records to the output buffer</summary>
    public override void CreateNewOutputRows()
    {
        //Get SSIS Variables
        string serviceDate = Variables.DATE;
        string workGroup = Variables.WORKGROUP;
        //Set Webservice URL
        string wUrl = "http://yourwebserviceaddress/api/Agent?date=" + serviceDate + "&action=getWorkGroupMetrics&webLogin=" + workGroup;

        try
        {
            //Call getWebServiceResult to return our WorkGroupMetric array
            WorkGroupMetric[outPutMetrics = GetWebServiceResult(wUrl);

            //For each group of metrics output records
            foreach (var metric in outPutMetrics)
            {
                Output0Buffer.AddRow();
                Output0Buffer.WORKGROUPNAME = metric.CN;
                Output0Buffer.INTERVALTIME = Convert.ToDateTime(Variables.DATE + " " + metric.IN);
                Output0Buffer.CALLSOFFERED = metric.CO;
                Output0Buffer.CALLSANSWERED = metric.CA;
                Output0Buffer.CALLSABANDONED = metric.AB;
            }

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

    }

    /// <summary>
    /// Method to return our WorkGroupMetric array
    /// </summary>
    /// <param name="wUrl">The web service URL to call</param>
    /// <returns>An array of WorkGroupMetric composed of the de-serialized JSON</returns>
    private WorkGroupMetric[] GetWebServiceResult(string wUrl)
    {

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

        try
        {
            //Test the connection
            if (httpWResp.StatusCode == HttpStatusCode.OK)
            {

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

                //Set jsonString using a stream reader
                using (StreamReader reader = new StreamReader(responseStream))
                {
                    jsonString = reader.ReadToEnd().Replace("\\""");
                    reader.Close();
                }

                //Deserialize our JSON
                JavaScriptSerializer sr = new JavaScriptSerializer();
                //JSON string comes in with a leading and trailing " that need to be removed for parsing to work correctly
                //The JSON here is serialized weird, normally you would not need this trim
                jsonResponse = sr.Deserialize<WorkGroupMetric[]>(jsonString.Trim('"'));

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

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

    }

    /// <summary>
    /// Outputs error message
    /// </summary>
    /// <param name="errorMsg">Full error text</param>
    private void FailComponent(string errorMsg)
    {
        bool fail = false;
        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
        compMetadata.FireError(1, "Error Getting Data From Webservice!"errorMsg"", 0, out fail);

    }

}
#endregion

#region JSON Class
//Class to hold our work group metrics
class WorkGroupMetric
{
    public string CN { getset; }

    public string IN { getset; }

    public int CO { getset; }

    public int CA { getset; }

    public int AB { getset; }
}
#endregion

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

  foreach (var metric in outPutMetrics)
            {
                Output0Buffer.AddRow();
                Output0Buffer.WORKGROUPNAME = metric.CN;
                Output0Buffer.INTERVALTIME = Convert.ToDateTime(Variables.DATE + " " + metric.IN);
                Output0Buffer.CALLSOFFERED = metric.CO;
                Output0Buffer.CALLSANSWERED = metric.CA;
                Output0Buffer.CALLSABANDONED = metric.AB;
            }

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


Figure 9. Data Flow

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


Figure 10. JSON Feed in Data Viewer 

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

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

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

245 comments:

  1. Hi Jim,

    You are awesome, your code saved my life!

    Thanks,
    Amber

    ReplyDelete
  2. very very usefull.
    thank you,
    ivone

    ReplyDelete
  3. Hi Jim,

    This is exactly what I need, but I'm new to C# and for some reason the
    "using System.Web.Script.Serialization;" is giving me an error of "The type of namespace name 'Script' does not exist in the namespace 'System.Web'"

    Also, the WorkGroupMetric[] gives me a similar error.

    Any ideas?

    ReplyDelete
    Replies
    1. I'll be happy to help you! Did you make sure to add the correct reference?

      i.e. System.Web.Extensions.dll

      Delete
    2. http://stackoverflow.com/questions/1156313/adding-system-web-script-reference-in-class-library

      Delete
    3. I updated the blog on how to add this reference, I hope this helps.

      Delete
    4. You are my own, personal savior, man.

      For those just landing on this blog, Jim really went above and beyond with his personal replies to my desperate cries for assistance, definitely an expert!

      Thanks again Jim!!

      Delete
    5. Glad you got it working man. Good luck with your project :)

      Delete
  4. Hi Jim,

    Nice post, thanks very much for sharing!

    My webservice status code returns ok, but I'm having a problem at this stage (I've swapped out your WorkGroupMetric[] for Ticket[] ):

    jsonResponse = sr.Deserialize(jsonString);

    The error I'm getting is:

    Error Getting Data From Webservice!: System.MissingMethodException: No parameterless constructor defined for type of 'Ticket[]'

    I've tried adding a parameterless constructor to my Ticket class, but no joy, I still get the same error:

    public Ticket()
    {
    }

    Have you got an ideas where I might be going wrong, I've looked at all the stackoverflow and other posts I can find on this error.

    Any guidance much appreciated!!

    ReplyDelete
  5. Can you show me an example of your json? Also you can paste that json in json2csharp.com to generate your classes. Probably an issue with incorrect class definitions for your json.

    ReplyDelete
  6. Hi Jim,

    Thanks, you were right. Using json2csharp.com to create my classes sorted my problem, I had nested JSON objects (as well as a few type mismatches) so needed to reference the root object.

    Thanks again for the post!!

    ReplyDelete
  7. Hi Jim,
    Thank you for this article. I'm getting an error and wonder if you can help me please?
    [Error Getting Data From Webservice!] Error: System.MissingMethodException: No parameterless constructor defined for type of 'WorkGroupMetric[]'.

    My Jason is like this:
    {"count":2,"cache":false,"environment":"live","remainingAccesses":231,"data":[{"date":"2013-12-09","service":"Be On","campaign_id":17088,"campaign_name":"DE Peek & Cloppenburg Review Urban Karaoke","project_id":119,"websites":3,"impressions":48,"views":1,"views_paid":0,"redirects":0,"playtoends":0},{"date":"2013-12-09","service":"Be On","campaign_id":17280,"campaign_name":"DE Telekom Technischer Support","project_id":137,"websites":4,"impressions":189,"views":47,"views_paid":0,"redirects":2,"playtoends":9}]}

    ReplyDelete
  8. You're referencing the nested object and ignoring the root object. I used json2csharp.com to generate these classes for you and used a variable as your url, so you can pass it in from an SSIS variable. Use this code:

    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;


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

    public override void CreateNewOutputRows()
    {
    string wUrl = Variables.wUrl;

    try
    {
    RootObject outPutMetrics = getWebServiceResult(wUrl);

    foreach (var metric in outPutMetrics.data)
    {
    Output0Buffer.AddRow();
    Output0Buffer.Date = metric.date;
    Output0Buffer.Service = metric.service;
    Output0Buffer.CampaignID = metric.campaign_id;
    Output0Buffer.CampaignName = metric.campaign_name;
    Output0Buffer.ProjectID = metric.project_id;
    Output0Buffer.Websites = metric.websites;
    Output0Buffer.Impressions = metric.impressions;
    Output0Buffer.Views = metric.views;
    Output0Buffer.ViewsPaid = metric.views_paid;
    Output0Buffer.Redirects = metric.redirects;
    Output0Buffer.PlayToEnds = metric.playtoends;
    }

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

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

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

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

    JavaScriptSerializer sr = new JavaScriptSerializer();
    jsonResponse = sr.Deserialize(jsonString);

    }

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

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

    }

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

    }
    }

    public class Datum
    {
    public DateTime date { get; set; }
    public string service { get; set; }
    public int campaign_id { get; set; }
    public string campaign_name { get; set; }
    public int project_id { get; set; }
    public int websites { get; set; }
    public int impressions { get; set; }
    public int views { get; set; }
    public int views_paid { get; set; }
    public int redirects { get; set; }
    public int playtoends { get; set; }
    }

    public class RootObject
    {
    public int count { get; set; }
    public bool cache { get; set; }
    public string environment { get; set; }
    public int remainingAccesses { get; set; }
    public List data { get; set; }
    }

    ReplyDelete
    Replies
    1. After data you need "<" Datum ">" (remove quotes ) looks like these are being removed in comments and for javascriptserializer "<"RootObject">"

      Delete
  9. I have a similar string I am trying to consume in SSIS, but since I kept getting errors I thought I would just try the code you wrote for Jaysen. I get an error in your code for Jaysen. I copied your code and made these changes you suggested.

    for the line
    public List data { get; set; }

    I changed it to (without the quote marks)
    public List"<"Datum">" data { get; set; }

    but I get an error - Syntax error, '(' expected - and it highlights the bracket to the left of get

    Also where exactly does javascriptserializer "<"RootObject">" go


    ReplyDelete
    Replies
    1. Can you send me your code?

      Delete
    2. My code was over the limit to post here, I "shared" it to you on google+
      Thanks

      Delete
    3. Thank you for all your help.

      I wandered all over the web looking for a clean solution to my import problem. Yours is the best I have found.

      With the code changes/additions you helped me with I now have a fast and reliable way to import my JSON feed into my SQL Server.

      Delete
    4. I too have the same issue now.I tried giving jsonResponse = sr.Deserialize(jsonString); but still not working.Please help

      Delete
  10. Hi Jim,

    I too seem to be having the same problem as Mike - "Error: System.MissingMethodException: No parameterless constructor defined for type of 'RootObject[]".

    Here is an example of my JSON - am hoping you can give me some pointers as I have been trying all day to get this to work, without much success !!

    {
    "downloads": 829,
    "updates": 3989,
    "returns": 0,
    "net_downloads": 829,
    "promos": 0,
    "revenue": "0.00",
    "edu_downloads": 0,
    "gifts": 0,
    "gift_redemptions": 0
    }


    Cheers
    Paul

    ReplyDelete
  11. ... RootObject[] being a replacement for WorkGroupMetric[] btw

    ReplyDelete
  12. I copied your code to json2csharp.com and got this:
    public class RootObject
    {
    public int downloads { get; set; }
    public int updates { get; set; }
    public int returns { get; set; }
    public int net_downloads { get; set; }
    public int promos { get; set; }
    public string revenue { get; set; }
    public int edu_downloads { get; set; }
    public int gifts { get; set; }
    public int gift_redemptions { get; set; }
    }

    If you still cant get it to work email me what you have so far and Ill take a look at your code

    ReplyDelete
  13. Hi Jim,

    Actually I did get it to work. The issue wasn't with the the JSON or the Class it was with the fact that only one record was being returned on each call. I simply got rid of the For Each loop and hey presto

    Thanks for the quick reply

    Paul

    ReplyDelete
  14. hi there,able to get connect to services,but my "jsonresponce" variable is not holdingany data.however is loop through all row,but values are 0 for int datatype and null for string datatype in my data viewer.

    ReplyDelete
    Replies
    1. Can you email me your code? Have you done a null test on your object?

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

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

      Delete
    4. In your code, your class definition is wrong. I pasted your JSON to json2csharp.com. Change it to this:

      public class WorkGroupMetric
      {
      public string EmailId { get; set; }
      public string FirstName { get; set; }
      public int Id { get; set; }
      public string LastName { get; set; }
      public int OrganizationId { get; set; }
      public string Password { get; set; }
      public int UserTypeId { get; set; }
      }

      Then you'll need to update your Output0Buffer code to something like this:

      Output0Buffer.AddRow();
      Output0Buffer.ID = metric.Id ;
      Output0Buffer.NAME = metric.LastName ;
      Output0Buffer.ORGAZINATIONID = metric.OrganizationId;

      Delete
    5. sorry its was testing alternative url.
      my class was for different url
      pls, replace "getusers" with "getteams" in url
      and check and let know what is wrong

      Delete
    6. For the second URL you sent me your class needs to be:

      public class WorkGroupMetric
      {
      public int Id { get; set; }
      public string Name { get; set; }
      public int OrganizationId { get; set; }
      }

      Your Output0Buffer code needs to be:

      Output0Buffer.AddRow();
      Output0Buffer.ID = metric.Id ;
      Output0Buffer.NAME = metric.Name ;
      Output0Buffer.ORGAZINATIONID = metric.OrganizationId;

      Delete
  15. that's exactly how my class is,i don't have problem with calss itself.
    But when you deserialized data and assign it to variable which in your example is jsonresponce, Its return null

    ReplyDelete
    Replies
    1. Did you use the same names for attributes in your class or is it still:

      class WorkGroupMetric
      {
      public int i { get; set; }

      public string N { get; set; }

      public int O_id { get; set; }

      }

      Delete
  16. thank you for that attention, I thought we can change the name of attribute since we using in our class , now I figure that attributes has to be excate same as in json file.
    thank you

    ReplyDelete
  17. Great Article!! I have question and I don't have much experience in C#. What if the URL source needs authentication permission to login? How would you change your code?

    ReplyDelete
  18. Well it depends on your api. I've see something like this for network credentials:
    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create("URL Goes HERE");
    httpWReq.Credentials = new NetworkCredential("UserName", "Password");

    I've also had to send user names and passwords in the header like this:

    public string Authentication
    {
    get
    {
    byte[] bytes = Encoding.Default.GetBytes(String.Format("{0} : {1}", "UserName", "Password"));
    string _authentication = "Basic " + Convert.ToBase64String(bytes);
    return _authentication;
    }
    }

    HttpWebRequest httpWReq = (HttpWebRequest)HttpWebRequest.Create("URL Goes HERE");
    httpWReq.Headers["Authorization"] = Authentication;

    But i've also had to deal with different SOAP and REST api's that each have a different way they want credentials handled. Your best bet is to look into any documentation for the API of this web service. Is this a public API?

    ReplyDelete
  19. Hi Jim,
    I have been going through this thread for quite some time and I am now stuck at the Error: foreach statement cannot operate on variables of type 'RootObject' because 'RootObject' does not contain a public definition for 'GetEnumerator'

    Here is my code:

    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;



    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

    public class ScriptMain : UserComponent

    {


    public override void CreateNewOutputRows()

    {

    string wUrl = "https://api.pipelinedeals.com/api/v3/users.json?api_key=9Tp1QBPX0uKDnn751mi";


    try

    {

    RootObject outPutMetrics = getWebServiceResult(wUrl);


    foreach (var metric in outPutMetrics)

    {


    Output0Buffer.AddRow();

    Output0Buffer.accountid= metric.account_id;

    Output0Buffer.email = metric.email;

    Output0Buffer.email2 = metric.email2;

    Output0Buffer.email3 = metric.email3;

    Output0Buffer.firstname = metric.first_name;

    Output0Buffer.id = metric.id;

    Output0Buffer.isaccountadmin = metric.is_account_admin;

    Output0Buffer.lastname = metric.last_name;

    Output0Buffer.avatarthumburl = metric.avatar_thumb_url;

    Output0Buffer.apikey= metric.api_key;





    }


    }

    catch (Exception e)

    {

    failComponent(e.ToString());

    }


    }



    private RootObject getWebServiceResult(string wUrl)

    {


    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create("https://api.pipelinedeals.com/api/v3/users.json?api_key=9Tp1QBPX0uKDnn751mi");

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

    RootObject jsonResponse = null;


    try
    {


    if (httpWResp.StatusCode == HttpStatusCode.OK)

    {


    Stream responseStream = httpWResp.GetResponseStream();

    string jsonString;


    using (StreamReader reader = new StreamReader(responseStream))

    {

    jsonString = reader.ReadToEnd();

    reader.Close();

    }

    JavaScriptSerializer sr = new JavaScriptSerializer();

    jsonResponse = sr.Deserialize(jsonString);


    }


    else
    {

    failComponent(httpWResp.StatusCode.ToString());


    }

    }

    catch (Exception e)

    {

    failComponent(e.ToString());

    }

    return jsonResponse;


    }


    private void failComponent(string errorMsg)

    {

    bool fail = false;

    IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

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


    }

    }

    public class Entry

    {

    public int account_id { get; set; }

    public string email { get; set; }

    public string email2 { get; set; }

    public string email3 { get; set; }

    public string first_name { get; set; }

    public int id { get; set; }

    public bool is_account_admin { get; set; }

    public string last_name { get; set; }

    public string avatar_thumb_url { get; set; }

    public string api_key { get; set; }

    }

    public class Pagination

    {

    public int page { get; set; }

    public string page_var { get; set; }
    public int per_page { get; set; }

    public int pages { get; set; }

    public int total { get; set; }

    }


    public class RootObject

    {

    public List entries { get; set; }

    public Pagination pagination { get; set; }

    }

    ReplyDelete
    Replies
    1. Change you class definition for RootObject to this:

      public class RootObject
      {

      public List < Entry > entries { get; set; }

      public Pagination pagination { get; set; }

      }

      Your foreach line should then change to this:

      foreach (var metric in outPutMetrics.entries)

      Delete
    2. You'll also want to change this line:

      jsonResponse = sr.Deserialize(jsonString);

      To this line:
      jsonResponse = sr.Deserialize< RootObject >(jsonString);

      Delete
    3. yes I figured the and the corrections earlier, But still, thank you! :)

      Delete
  20. Jim, I cannot thank you enough. I was missing ".entries" in the statement below:

    foreach (var metric in outPutMetrics.entries)

    You made my day! Thank you so much.

    ReplyDelete
  21. Jim,
    If the api has a hard limit on the number of results it would return for each request, how do i get my code to automatically check and iterate through multiple paginations to address this hurdle?

    ReplyDelete
    Replies
    1. Does the api have documentation? Usually it will tell you how to paginate and when you've come to the end of the record set.

      Delete
  22. It does. It provides a series of links for each page. However, I am trying to automate the whole process using the SSIS package I put together using your script. So basically a process that iterates through each page after determining how many pages to go through. I am actually a novice in c# and SSIS and so I don't know how to attack the problem. Your code helped me understand quite a bit and I was wondering if I need to tinker with the pagination code or do I need a for loop container, and if the latter then what kind of code should be incorporated in the for loop container. Can you help? Thanks.

    ReplyDelete
  23. Hi Jim,
    I tried doing this but to no avail:
    do {

    try
    {
    RootObject outPutMetrics = getWebServiceResult(wUrl);

    foreach (var metric in outPutMetrics.entries)
    {
    Output0Buffer.AddRow();
    Output0Buffer.closedtime = metric.closed_time;
    Output0Buffer.companyid = metric.company_id;
    Output0Buffer.createdat = metric.created_at;
    Output0Buffer.dealstageid = metric.deal_stage_id;
    Output0Buffer.expectedclosedate = metric.expected_close_date;
    Output0Buffer.expectedclosedateeventid = metric.expected_close_date_event_id;
    Output0Buffer.id = metric.id;
    Output0Buffer.importid = metric.import_id;
    Output0Buffer.isarchived = metric.is_archived;
    Output0Buffer.isexample = metric.is_example;
    Output0Buffer.name = metric.name;
    Output0Buffer.primarycontactid = metric.primary_contact_id;
    Output0Buffer.probability = metric.probability;
    Output0Buffer.sourceid = metric.source_id;
    Output0Buffer.status = metric.status;
    Output0Buffer.summary = metric.summary;
    Output0Buffer.updatedat = metric.updated_at;
    Output0Buffer.userid = metric.user_id;
    Output0Buffer.value = metric.value;
    Output0Buffer.valueincents = metric.value_in_cents;
    }

    }
    catch (Exception e)
    {
    failComponent(e.ToString());
    }
    page++;
    }
    while (per_page == 900 );

    ReplyDelete
  24. Yeah I looked and they have documentation on pagination on their website. I can probably dig into this a little more tomorrow and see where I get with it.

    ReplyDelete
  25. This should work for you. Pay special attention to the way I handled the output for companyid. You will need to protect yourself from null values or you will get an exception. I would suggest doing this to any object coming back that has the potential to be null. Also your class definitions may be different than mine since I didn't fill out a lot of the data, which should be fine, just replace my class definitions with your own:

    ReplyDelete
  26. 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;



    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

    public class ScriptMain : UserComponent
    {


    public override void CreateNewOutputRows()
    {

    int totalPages = 1;
    int iterator = 0;
    bool setYet = false;

    //At least run once
    while (iterator < totalPages)
    {


    string wUrl = String.Format("https://api.pipelinedeals.com/api/v3/deals.json?page={0}&per_page=1&api_key=XXXXXXXXXXX", (iterator + 1).ToString());


    try
    {
    //Get first results
    RootObject outPutMetrics = getWebServiceResult(wUrl);


    if (!setYet)
    {
    totalPages = outPutMetrics.pagination.pages;
    }


    foreach (var metric in outPutMetrics.entries)
    {


    Output0Buffer.AddRow();

    Output0Buffer.closedtime = metric.closed_time;
    if (metric.company != null)
    {

    Output0Buffer.companyid = metric.company_id;
    }
    else
    {
    Output0Buffer.companyid_IsNull = true;
    }
    Output0Buffer.createdat = metric.created_at;
    Output0Buffer.dealstageid = metric.deal_stage_id;
    Output0Buffer.expectedclosedate = metric.expected_close_date;
    Output0Buffer.expectedclosedateeventid = metric.expected_close_date_event_id;
    Output0Buffer.id = metric.id;
    Output0Buffer.importid = metric.import_id;
    Output0Buffer.isarchived = metric.is_archived;
    Output0Buffer.isexample = metric.is_example;
    Output0Buffer.name = metric.name;
    Output0Buffer.primarycontactid = metric.primary_contact_id;
    Output0Buffer.probability = metric.probability;
    Output0Buffer.sourceid = metric.source_id;
    Output0Buffer.status = metric.status;
    Output0Buffer.summary = metric.summary;
    Output0Buffer.updatedat = metric.updated_at;
    Output0Buffer.userid = metric.user_id;
    Output0Buffer.value = metric.value;
    Output0Buffer.valueincents = metric.value_in_cents;

    }

    iterator++;
    }

    catch (Exception e)
    {

    failComponent(e.ToString());

    }

    }
    }

    ReplyDelete
  27. private RootObject getWebServiceResult(string wUrl)
    {


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

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

    RootObject jsonResponse = null;


    try
    {


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


    Stream responseStream = httpWResp.GetResponseStream();

    string jsonString;


    using (StreamReader reader = new StreamReader(responseStream))
    {

    jsonString = reader.ReadToEnd();

    reader.Close();

    }

    JavaScriptSerializer sr = new JavaScriptSerializer();

    jsonResponse = sr.Deserialize< RootObject >(jsonString);


    }


    else
    {

    failComponent(httpWResp.StatusCode.ToString());


    }

    }

    catch (Exception e)
    {

    failComponent(e.ToString());

    }

    return jsonResponse;


    }


    private void failComponent(string errorMsg)
    {

    bool fail = false;

    IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

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


    }

    }

    public class User
    {
    public int id { get; set; }
    public string first_name { get; set; }
    public string last_name { get; set; }
    }

    public class Source
    {
    public int id { get; set; }
    public string name { get; set; }
    }

    public class DealStage
    {
    public int id { get; set; }
    public string name { get; set; }
    public int percent { get; set; }
    }

    public class Company
    {
    public int id { get; set; }
    public string name { get; set; }
    }

    public class CustomFields
    {
    public int custom_label_983660 { get; set; }
    public int custom_label_983661 { get; set; }
    }

    public class Entry
    {
    public object closed_time { get; set; }
    public int? company_id { get; set; }
    public string created_at { get; set; }
    public int? deal_stage_id { get; set; }
    public string expected_close_date { get; set; }
    public int expected_close_date_event_id { get; set; }
    public int id { get; set; }
    public object import_id { get; set; }
    public bool is_archived { get; set; }
    public object is_example { get; set; }
    public string name { get; set; }
    public object primary_contact_id { get; set; }
    public int probability { get; set; }
    public int source_id { get; set; }
    public int status { get; set; }
    public string summary { get; set; }
    public string updated_at { get; set; }
    public int user_id { get; set; }
    public double value { get; set; }
    public int value_in_cents { get; set; }
    public User user { get; set; }
    public List< object > people { get; set; }
    public Source source { get; set; }
    public DealStage deal_stage { get; set; }
    public object primary_contact { get; set; }
    public Company company { get; set; }
    public List< object > collaborators { get; set; }
    public List< object > shared_user_ids { get; set; }
    public List< object > person_ids { get; set; }
    public CustomFields custom_fields { get; set; }
    }

    public class Pagination
    {
    public int page { get; set; }
    public string page_var { get; set; }
    public int per_page { get; set; }
    public int pages { get; set; }
    public int total { get; set; }
    }

    public class RootObject
    {
    public List< Entry > entries { get; set; }
    public Pagination pagination { get; set; }
    }

    ReplyDelete
  28. Correction, to make this cleaner change this:

    if (!setYet)
    {
    totalPages = outPutMetrics.pagination.pages;
    }

    to this:
    if (!setYet)
    {
    totalPages = outPutMetrics.pagination.pages;
    setYet=true;
    }

    You could probably find a better way to do this, I just whipped up this code real quick.

    ReplyDelete
  29. Jim, Thank you for the above help.
    Rookie question: The part above 'to make this cleaner', I couldn't figure out where to insert that (I am a novice in C#). But I do get the crux of what you did. I do get 710 results which is more than the 200 hard limit set by the api provider, but the total results number 3580. Also, the data doesn't seem to get inserted into my tables. I can probably investigate that part and figure it out myself.
    ---------------------------------------------------------------------------------------------
    I implemented a solution that worked. I added 4 new variables: x, Loop, wUrl and RowCount. Initialized both x and Loop to 1. I configured the expression for wUrl within the variables section such that the page number is assigned to a variable x. Then I configured a for loop container that would execute as long as the loop variable (Loop) value is greater than 200. in my data flow, I added a row count and a second script component. The row count returned a value to a variable (RowCount) that i equated to the loop variable in the for loop container's "AssignExpression". The second script component incremented the value of another variable (x) that referred to the page number in the API access link.

    So the entire process flows as follows:
    Loop Starts at value 1 and as the process executes, the RowCount passes its value to Loop and makes it 200. Variable x gets incremented from 1 to 2. Since that value is > 0, the loop executes again and the process continues till RowCount = 0 (as in no more results left to read).
    That's it. :)
    Again, thanks for all the help. :D

    ReplyDelete
  30. Hello Jim, thanks very much for your posting. It has helped a lot as I am new to JSON in SSIS. I do have a problem with the following JSON. I do not know how to handle the public List object history {get; set:}. Any assistance would be greatly appreciated. Thanks {"found":true,"next_token":"10000-07,0","history":[]}

    ReplyDelete
    Replies
    1. Do you need to add the objects int the list to an output? If so do you have an example of what history looks like with data in it? By default it labels it an object, because there is no way for the parser to know what kind of object actually sits in the history array. If you don't need to get data out of it just leave it in the class definition, and leave it off of your outputs.

      Delete
    2. Hi Jim,

      It would be helpful if you could tell us how to handle Object type here.

      Delete
  31. My jason file is in this format - [{"ID":1,:"Name":"test1","State":"AP"},{"ID":2,:"Name":"test2","State":"KN"}{"ID":3,:"Name":"test3","State":null}]

    I have 1000 records, for easy understanding, i have posted 3 records only here. I have jason.txt file and i want to put this text file data into sql server. How do I export jason.txt to sql server using above code?

    ReplyDelete
    Replies
    1. Have you been able to get this into a blob (varchar(max)) in the data flow from a flat file connection yet?

      Delete
  32. Hi Jim,
    Can this code run against the HTTPS connection ?
    its giving error : unable to establish secured connection

    ReplyDelete
    Replies
    1. It can but you will need to add the code specific to connecting to the HTTPS connection to your code.

      Delete
  33. Could you please provide me the code .
    Also ,Could you please let me know if by installing the certificate, Can this problem be resolved

    ReplyDelete
    Replies
    1. Does this webservice you are connecting to have any documentation on how it wants to be authenticated against?

      Delete
  34. Hi Jim,
    I need to authenticate the url with user and pass in the first call and then access another url which will provide me the json data.how to do multiple pass .
    for ex:In the browser, i access the url to authenticate first : https://webaddress/auth?name=datascience&pass=datascience
    the access code is stored in cookie and after this i call the api where i have data : https://webaddress/v1/group/name="helius"

    how to achieve the same by doing multiple pass and getting the json feed.

    ReplyDelete
    Replies
    1. Can you add this information to the header?

      Delete
  35. i am struck at a point where Output0Buffer is not being recognised.
    error: The name 'TaskDataBuffer' does not exist in the current context
    how to resolve this?

    ReplyDelete
  36. Jim,

    This has been really helpful so far. I used your code to parse a simple JSON. Now i have a more complex one... with some sort of nesting. I used json2csharp to create the following classes, but im not sure how to walk thru them in a loop to write them to a database. Im getting an error when i try to call up DataPoints.carrier_used .

    Below are the classes, the loop and below that is the JSON. let me know if you can help. I can send you all the code if you like. just let me know how to share it.

    thanks so much!!!

    dan

    public class Tags
    {
    public string carrier_used { get; set; }
    public string cycle { get; set; }
    public string category { get; set; }
    }

    public class RootObject
    {
    public string MetricName { get; set; }
    public Tags Tags { get; set; }
    public List> DataPoints { get; set; }
    }



    foreach (var metric in outPutMetrics.DataPoints)
    {
    Output0Buffer.AddRow();
    Output0Buffer.CARRIERUSED = metric.carrier_used;
    }


    [{"MetricName":"atb.label_paid.STATE.60s","Tags":{"carrier_used":"USPS","cycle":"60","category":"subtotal"},"DataPoints":[[1428260400,357],[1428260460,314],[1428260520,371],[1428260580,370],[1428260640,311],[1428260700,348],[1428260760,387],[1428260820,303],[1428260880,323],[1428260940,354],[1428261000,333],[1428261060,291],[1428261120,305],[1428261180,307],[1428261240,346],[1428261300,310],[1428261360,322],[1428261420,313],[1428261480,346],[1428261540,311],[1428261600,362],[1428261660,365],[1428261720,301],[1428261780,358],[1428299580,253],[1428299640,240],[1428299700,199],[1428299760,186],[1428299820,212],[1428299880,215],[1428299940,298],[1428300000,280]]}]

    ReplyDelete
    Replies
    1. Couple questions. For Tags will there always be carrier_used, cycle and category...or can there be more or less? For DataPoints, this will have to be in a separate output because its in a different level of granularity than the RootObject. Is the MetricName, and the 2 digits within every array a unique identifier? Do we need to add some kind of auto-generated number for these? i.e.

      1 atb.label_paid.STATE.60s 1428260400 357
      2 atb.label_paid.STATE.60s 1428260460 314

      ..etc.

      Delete

    2. Hi Jim,
      First off, awesome blog! This information has been crucial recently.

      I'm now running into the same problem as the user above with more compete json. Here's an example of the JSON I'm working with:
      {
      "totalcount":2,
      "problems":[
      {
      "problemid":"1462",
      "events":[
      {
      "source":"HISTORY",
      "eventtype":"START",
      "startdate":"09\/23\/2014",
      "diagnoses":[

      ]
      }
      ],
      "codeset":"SNOMED",
      "name":"Carpal tunnel syndrome",
      "code":"57406009"
      },
      {
      "problemid":"2986",
      "events":[
      {
      "source":"HISTORY",
      "eventtype":"START",
      "startdate":"05\/14\/2015",
      "laterality":"LEFT",
      "note":"bad",
      "diagnoses":[

      ]
      }
      ],
      "codeset":"SNOMED",
      "name":"Carpal tunnel syndrome",
      "code":"57406009"
      }
      ]
      }

      I'm having trouble accessing the events array within the problems array...I guess the events would be considers an inner root? I've been using the json2csharp tool to create a c# classes.
      I understand that the events array would need to be written to a second output buffer, but I'm not understanding how to point to the inner array (events). Below is my failed attempt at looping
      through the events.

      foreach (var metric in outPutMetrics.problems)
      {
      Output0Buffer.AddRow();
      Output0Buffer.problemid = metric.problemid;
      Output0Buffer.codeset = metric.codeset;
      Output0Buffer.name = metric.name;
      Output0Buffer.code = metric.code;

      }
      foreach (var metric in outPutMetrics.events)
      {


      Output1Buffer.AddRow();
      Output1Buffer.source = metric.source;
      Output1Buffer.eventtype = metric.eventtype;
      Output1Buffer.startdate = metric.startdate;

      if (metric.laterality != null)
      {
      Output1Buffer.laterality = metric.laterality;
      }
      else
      {
      Output1Buffer.laterality = "N/A";
      }

      }

      Thank you for any help you can provide and once again, awesome blog post!

      Nate

      Delete
    3. Thank you for your kind words. Does changing the code to this work?

      foreach (var metric in outPutMetrics.problems)
      {
      Output0Buffer.AddRow();
      Output0Buffer.problemid = metric.problemid;
      Output0Buffer.codeset = metric.codeset;
      Output0Buffer.name = metric.name;
      Output0Buffer.code = metric.code;


      foreach (var event in metric.events)
      {


      Output1Buffer.AddRow();
      Output1Buffer.source = event.source;
      Output1Buffer.eventtype = event.eventtype;
      Output1Buffer.startdate = event.startdate;

      if (event.laterality != null)
      {
      Output1Buffer.laterality = event.laterality;
      }
      else
      {
      Output1Buffer.laterality = "N/A";
      }

      }
      }

      Delete
    4. Jim, you are the man! Thanks for the quick response!

      Delete
    5. Hi Jim,
      One more silly question. From the JSON snippet I posted in my first question, how can I access the the totalCount key? I want to either assign this to a readwrite variable or include it in a separate output buffer. This count will be used as a validation on the number or rows I pump out.

      Thanks again!
      Nate

      Delete
    6. Probably something like this:

      if(outPutMetrics !=null)
      {
      Output2Buffer.AddRow();
      Output2Buffer.totalcount=outPutMetrics.totalcount;

      foreach (var metric in outPutMetrics.problems)
      {
      Output0Buffer.AddRow();
      Output0Buffer.problemid = metric.problemid;
      Output0Buffer.codeset = metric.codeset;
      Output0Buffer.name = metric.name;
      Output0Buffer.code = metric.code;


      foreach (var event in metric.events)
      {


      Output1Buffer.AddRow();
      Output1Buffer.source = event.source;
      Output1Buffer.eventtype = event.eventtype;
      Output1Buffer.startdate = event.startdate;

      if (event.laterality != null)
      {
      Output1Buffer.laterality = event.laterality;
      }
      else
      {
      Output1Buffer.laterality = "N/A";
      }

      }
      }
      }

      Delete
    7. Thanks for all of your help!
      Nate

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

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

    ReplyDelete
  39. Hello,
    Really you have share great information. This will be very helpful.We are also having same services and provide standards tools likeSSIS PowePack Standard Edition,ZappyShell Standard Edition ,SSIS Json SSIS json source etc.

    ReplyDelete
  40. 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().Replace("\\", "");
    reader.Close();
    }

    //Deserialize our JSON
    JavaScriptSerializer sr = new JavaScriptSerializer();
    //JSON string comes in with a leading and trailing " that need to be removed for parsing to work correctly
    //The JSON here is serialized weird, normally you would not need this trim
    jsonResponse = sr.Deserialize(jsonString.Trim('"'));

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

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


    }


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

    }

    }
    #endregion

    #region JSON Class
    public class Crime
    {
    public int cdid { get; set; }
    public string type { get; set; }
    public string date { get; set; }
    public string address { get; set; }
    public string link { get; set; }
    public double lat { get; set; }
    public double lon { get; set; }
    }

    public class RootObject
    {
    public List crimes { get; set; }
    }
    #endregion

    ATTACHED is my script. However I am getting an exception :
    No parameterless constructor defined for type of 'RootObject[]'.


    PLEASE HELP!!

    ReplyDelete
    Replies
    1. Can you supply some sample json ?

      Delete
    2. {"crimes":[{"cdid":65464808,"type":"Assault","date":"07/19/15 10:27 AM","address":"2700 BLOCK OF SPRUCEWOOD STREET #B","link":"http://spotcrime.com/crime/65464808-eead1acf95d5b94e5c12c31b959258d6","lat":30.638551,"lon":-96.37403},{"cdid":65464813,"type":"Robbery","date":"07/19/15 06:02 AM","address":"1100 BLOCK OF BITTLE LANE","link":"http://spotcrime.com/crime/65464813-dfb71bdb98be0165927bc9887799cd6d","lat":30.651218,"lon":-96.380641}]}

      Delete
    3. remove RootObject[]. You only need RootObject, the object coming back is not an array but a single object that contains an array.

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

    ReplyDelete
  42. Hello Jim,

    How would loop work for nested classes?

    public class Product
    {
    public string brand { get; set; }
    public string description { get; set; }
    public string color { get; set; }

    }

    public class VendorProducts
    {
    public Product product { get; set; }

    }

    public class InventorySetup
    {
    public string vendor { get; set; }
    public VendorProducts vendorProducts { get; set; }

    }

    public class RootObject
    {
    public InventorySetup InventorySetup { get; set; }
    public List data { get; set; }
    }
    foreach (var metric in outPutMetrics.data)
    {

    Output0Buffer.AddRow();
    Output0Buffer.vendor = metric.vendor;
    }

    ReplyDelete
    Replies
    1. I am getting following error "Object reference not set to an instance of an object."

      Delete
    2. Can you post the code you have tried so far?

      Delete
  43. Hi Jim. This has been a fantastic resource! I hope you're still checking on comments here as I'd love to get your help on some probably stupid first time errors with my code.

    I am getting two errors

    [Error Getting Data From Webserivce!] Error: System.ArgumentException: Invalid JSON primitive: ....
    at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializePrimitiveObject()
    at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializeInternal(Int32 depth)
    at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializeList(Int32 depth)
    at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializeInternal(Int32 depth)
    at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializeDictionary(Int32 depth)
    at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializeInternal(Int32 depth)
    at System.Web.Script.Serialization.JavaScriptObjectDeserializer.BasicDeserialize(String input, Int32 depthLimit, JavaScriptSerializer serializer)
    at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize(JavaScriptSerializer serializer, String input, Type type, Int32 depthLimit)
    at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize[T](String input)
    at ScriptMain.GetWebServiceResult(String wUrl)

    and

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

    Thank you so much for all of your blogs and your help in this project! I greatly appreciate your time.

    The json is here:
    {
    "request": {
    "status_code":200,
    "status_message":"OK",
    "path":"/external/v1/places",
    "method":"GET",
    "params":{}
    },
    "paging": {
    "next_page":null,
    "current_page":1,
    "previous_page":null,
    "per_page":25,
    "total_records":204,
    "first_timestamp":1403156620.0609322,
    "last_timestamp":1400008286.827591
    },
    "data":[
    {
    "address":"695 Aliya Forest",
    "city":"Everettmouth",
    "country":"US",
    "created_at":"2014-05-13T15:11:26-04:00",
    "custom_place_id":null,
    "id":18,
    "name":"Rock Nation place #0",
    "postal_code":"37605-0754",
    "state":"CA",
    "updated_at":"2014-06-19T01:43:40-04:00"
    },

    ]
    }

    ReplyDelete
  44. #region Class
    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
    //Outputs records to the output buffer
    public override void CreateNewOutputRows()
    {
    //Set Webservice URl
    string wUrl = "http://private-anon-3441cdbce-gsc.apiary-mock.com/external/v1/places";

    try
    {
    //Call getWebServiceResult to return WorkGroupMetric array
    RootObject outPutMetrics = GetWebServiceResult(wUrl);

    //For each group of metrics output records
    foreach (var metric in outPutMetrics.data)
    {
    Output0Buffer.AddRow();
    Output0Buffer.Country = metric.country;
    Output0Buffer.PostalCode = metric.postal_code;
    Output0Buffer.State = metric.state;
    Output0Buffer.City = metric.city;
    Output0Buffer.Address = metric.address;
    Output0Buffer.Name = metric.name;
    }
    }
    catch (Exception e)
    {
    FailComponent(e.ToString());
    }
    }

    //Method to return WorkGroupMetric array
    //param name ="wUrl" The web service URL to call
    //return An array of WorkGroupMetric composed of the de-serialized JSON

    private RootObject GetWebServiceResult(string wUrl)
    {
    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    RootObject jsonResponse = null;

    try
    {
    //Test 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 JSON
    JavaScriptSerializer sr = new JavaScriptSerializer();
    jsonResponse = sr.Deserialize(jsonString);

    }
    //Output connection error message
    else
    {
    FailComponent(httpWResp.StatusCode.ToString());
    }
    }
    //Outpout 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 Webserivce!", errorMsg, "", 0, out fail);
    }
    }
    #endregion

    #region JSON Class

    public class Params
    {
    }

    public class Request
    {
    public int status_code { get; set; }
    public string status_message { get; set; }
    public string path { get; set; }
    public string method { get; set; }
    public Params @params { get; set; }
    }

    public class Paging
    {
    public object next_page { get; set; }
    public int current_page { get; set; }
    public object previous_page { get; set; }
    public int per_page { get; set; }
    public int total_records { get; set; }
    public double first_timestamp { get; set; }
    public double last_timestamp { get; set; }
    }

    public class Datum
    {
    public string address { get; set; }
    public string city { get; set; }
    public string country { get; set; }
    public string created_at { get; set; }
    public int custom_place_id { get; set; }
    public int id { get; set; }
    public string name { get; set; }
    public string postal_code { get; set; }
    public string state { get; set; }
    public string updated_at { get; set; }
    }

    public class RootObject
    {
    public Request request { get; set; }
    public Paging paging { get; set; }
    public List data { get; set; }

    }
    #endregion

    ReplyDelete
    Replies
    1. have you tried replacing public class Datum with public class data ? In RootObject you are referencing a list of type < data > but you have no class defined for that.

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

    ReplyDelete
  46. Hi Jim, Thanks for the blog post, it’s been really useful and definitely helped out massively as I’ve got limited experience with C#. I think I’ve got the script nearly there, but I’m getting a couple of errors when running it in SSIS. I’ve got a couple of invalid characters in my JSON (that are commented out at present), and I’m not sure how to handle them so they can be included unfortunately. I can't post my script here though, I'm getting an error, would you mind if I emailed it to you?

    Any help would be most appreciated!

    ReplyDelete
  47. Jim,

    I'm getting a JSON parsing error. "No parameterless constructor defined for type of 'RootObject[]'."

    The JSON Response is very simple.

    {
    "success": true,
    }

    My JSON Class:

    public class RootObject
    {
    public bool success { get; set; }
    }

    ReplyDelete
  48. Replies
    1. This is because you have your method returning an array, when it should be returning just the RootObject...i.e RootObject instead of RootObject[]

      Delete
  49. From the JSON API documentation that I have in regards to my API states HTTP Basic Auth. How would I incorporate this into the code you supplied?

    ReplyDelete
    Replies
    1. Would be difficult to determine without seeing the document. Does this help? http://stackoverflow.com/questions/707888/using-http-authentication-with-a-c-sharp-webrequest

      Delete
  50. Hi Jim,

    Thank you for this blog - it's a great source of information.
    Can you tell me how you deal with spaces in parsing json class objects? For example:

    You will notice it returns "Test Duration" and "Signal Strength"

    public class Data
    {
    public string SSID { get; set; }
    public string Test Duration { get; set; }
    public string Signal Strength { get; set; }

    ReplyDelete
    Replies
    1. It is kind of hard to do this with the JavascriptSerializer. I would use data contracts for this. I have a post here that describes how to use it: http://dennysjymbo.blogspot.com/2014/03/utilizing-net-40-datacontractjsonserial.html

      Delete
  51. Hi Jim,

    I dont know whether youa re still replying comments , but it will be very helpful for since i am struggling to get an answer for this in last few weeks
    My Json coming like this in two formats here is location is string in first and class in second

    {
    "result": [
    {
    "upon_approval": "Proceed to Next Task",
    "location": "",
    "expected_start": ""
    }
    ]
    }

    Class definition for this

    public class Result
    {
    public string upon_approval { get; set; }
    public string location { get; set; }
    public string expected_start { get; set; }
    }

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





    {
    "result": [
    {
    "upon_approval": "Proceed to Next Task",
    "location": {
    "display_value": "Corp-HQR",
    "link": "https://satellite.service-now.com/api/now/table/cmn_location/4a2cf91b13f2de00322dd4a76144b090"
    },
    "expected_start": ""
    }
    ]
    }


    Class definition for this

    public class Location
    {
    public string display_value { get; set; }
    public string link { get; set; }
    }

    public class Result
    {
    public string upon_approval { get; set; }
    public Location location { get; set; }
    public string expected_start { get; set; }
    }

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



    So here location is string and a class sometimes
    So when deserializing my Json string isthrowing error because i able to declare location in either one format only . So how can i dynamically get these two formats for Json

    This is how i am serializing

    JavaScriptSerializer ser = new JavaScriptSerializer();
    ser.MaxJsonLength = 2147483647;
    RootObject ro = ser.Deserialize(responseValue);

    ReplyDelete
    Replies
    1. Always declare it an object in the class definition, not a string. Then do a null check on it before attempting to access it in the Result object so you don't get an error.

      Delete
    2. http://stackoverflow.com/questions/37953102/json-value-is-sometimes-a-string-and-sometimes-an-object/37955660?noredirect=1#comment63485911_37955660

      Delete
  52. Hi JIM,
    i am so glad that i found your blog!!!
    I am new to C# and i am getting error for my JSON code when i pass my data thru json2csharp.com getting error


    I am not able to share my full length code here could you please share your email so that i can share my code.

    Thank you in advance.

    ReplyDelete
    Replies
    1. Hi Jim,

      here is my JSON

      public class Record
      {
      public string id { get; set; }
      public string opportunity_id { get; set; }
      public string account_id { get; set; }
      }

      public class RootObject
      {
      public List records { get; set; }
      public string totalCount { get; set; }
      public int result { get; set; }
      }

      Delete
    2. Those are the classes, can you share a sample of the JSON you're getting as well as the error you are getting?

      Delete
    3. JIM,

      Here is my JSON

      {"records":[{"id":"ccc9bb9f-1bd6-d155-85d7-577d79cfbcb1","opportunity_id":"a8f23db0-7834-1f6f-3a3a-577d79b426ef","account_id":"6b571ed7-ee43-4e68-dd45-569fed133b0f"},{"id":"4b3738ed-8b7e-b767-35b8-577c43d6ad72","opportunity_id":"24e469fc-82a3-c02c-4a1d-5779be83dca5","account_id":"ee20dc76-07b2-92fe-e217-558e0f9b9d3d"},{"id":"2c3fb40b-d5c9-9e56-7c17-5779be0cb1c2","opportunity_id":"f401f38b-7ada-ca0f-c45a-5779be662c8e","account_id":"6b571ed7-ee43-4e68-dd45-569fed133b0f"},{"id":"c3b325fa-788c-0227-365c-576b0e311aea"

      And the Error :

      [Error Getting Data From Webservice!] Error: System.UriFormatException: Invalid URI: The Authority/Host could not be parsed.
      at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
      at System.Net.WebRequest.Create(String requestUriString)
      at ScriptMain.GetWebServiceResult(String wUrl)
      at ScriptMain.CreateNewOutputRows()
      Thank you.

      Delete
    4. Have you tried copying the URL that your code is generating directly into a browser to see if that works?

      Delete
    5. Jim,

      yes I did, I paste the URL directly into the browser and i got data. sample data


      {"records":[{"id":"ccc9bb9f-1bd6-d155-85d7-577d79cfbcb1","opportunity_id":"a8f23db0-7834-1f6f-3a3a-577d79b426ef","account_id":"6b571ed7-ee43-4e68-dd45-569fed133b0f"},{"id":"4b3738ed-8b7e-b767-35b8-577c43d6ad72","opportunity_id":"24e469fc-82a3-c02c-4a1d-5779be83dca5","account_id":"ee20dc76-07b2-92fe-e217-558e0f9b9d3d"},{"id":"2c3fb40b-d5c9-9e56-7c17-5779be0cb1c2","opportunity_id":"f401f38b-7ada-ca0f-c45a-5779be662c8e","account_id":"6b571ed7-ee43-4e68-dd45-569fed133b0f"},{"id":"c3b325fa-788c-0227-365c-576b0e311aea"

      Delete
    6. JIM,

      But still how can i use this URL in my class...?

      if i use the direct URL in my class i am getting the following error

      [Error Getting Data From Webservice!] Error: System.UriFormatException: Invalid URI: The Authority/Host could not be parsed.
      at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
      at System.Net.WebRequest.Create(String requestUriString)
      at ScriptMain.GetWebServiceResult(String wUrl)
      at ScriptMain.CreateNewOutputRows()

      Delete
  53. Hi Jim,

    Some of the columns name I get returned in my Json are invalid (they have spaces) - do you know how I would handle this? Managed to get the rest of it working fine and it's returning results but NULLs in those columns that have invalid names.

    public class Datum
    {
    public string Date { get; set; }
    public string __invalid_name__Advertiser Group { get; set; }
    public string __invalid_name__Advertiser ID { get; set; }
    public string Advertiser { get; set; }
    public string __invalid_name__Campaign ID { get; set; }
    public string Campaign { get; set; }
    public string __invalid_name__Site ID (DCM) { get; set; }
    public string __invalid_name__Site (DCM) { get; set; }
    public string __invalid_name__Package/Roadblock ID { get; set; }
    public string __invalid_name__Package/Roadblock { get; set; }
    public string __invalid_name__Placement ID { get; set; }
    public string Placement { get; set; }
    public string __invalid_name__Ad ID { get; set; }
    public string Ad { get; set; }
    public string __invalid_name__Creative ID { get; set; }
    public string Creative { get; set; }
    public string Impressions { get; set; }
    public string Clicks { get; set; }
    }

    Thanks,

    Phil

    ReplyDelete
    Replies
    1. Hey Phil. It would be tough to do this using this serializer. I suggest you use the DataContractSerializer for this. You can call the attribute anything you want just add a metadata tag over it like so:

      [DataMember(Name = "Advertiser Group")]
      public string AdvertiserGroup { get; set; }

      I have a post that explains how to use this in SSIS:

      http://dennysjymbo.blogspot.com/2014/03/utilizing-net-40-datacontractjsonserial.html

      Delete
  54. Very Helpful Article. Thanks :) Great work

    ReplyDelete
  55. Hi ,

    is it possible to write delete statement in json.

    public String ConstructUpdate(Input0Buffer Row)
    {


    if (!Row.ISSUE_IsNull)
    {
    status = 321;

    }

    }

    this is how i am trying but doesn't seems working.

    ReplyDelete
    Replies
    1. Can you elaborate a little further on what you're trying to accomplish? Are you getting an error on a null value being sent to the output?

      Delete
  56. Hello Jim, thank you for sharing!!!
    I managed to get the response from openweathermap for all fields.
    How I will be able to insert that as one row in a table?Meaning how will I merge all the outputs?

    ReplyDelete
    Replies
    1. Can you share the JSON? I have an article I wrote about nested JSON arrays and an example of how to model them in a database here: http://dennysjymbo.blogspot.com/2013/11/deserializing-json-feed-with-nested.html

      Delete
  57. Hi Jim
    You seem to be the one and only person on earth that have used SSIS to read data from a REST API - and blogged about it :-). Is there somehow that I can get your help on doing a few things in SSIS Data flow task
    1.
    Use POST to get sessiontoken using
    https://api.website.com/login/application
    with
    Data: {"Name":"app1", "Key":"xyz"}
    2. Retrieve the from the Data response
    3. Use that SessionToken value in a GET
    http://api.website.com/salesdetail?startdate=2016-01-01&enddate=2016-01-02&securityToken=sessiontoken
    4.Retrieve the "Date","Employee","SalesAmount" from the Response data and output it into 3 columns in the script output for writing it into a SQL table

    ReplyDelete
    Replies
    1. Yeah that seems pretty easy. Where are you stuck?

      Delete
    2. Well i can get the data using the Simple REST Client. But first part is - how to do authentication, pass the token to the GET task ?
      Would you do two script tasks where first script task gets the token value to a column output, write to a variable that the next script tasks reads ?
      If i use your example above - pasting the token manually into the GET string while the session is open. I get this error
      Error: 0x1 at Data Flow Task, Error Getting Data From Webservice!: System.ArgumentException: Invalid JSON primitive: .
      at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializePrimitiveObject()
      at System.Web.Script.Serialization.JavaScriptObjectDeserializer.DeserializeInternal(Int32 depth)
      at System.Web.Script.Serialization.JavaScriptObjectDeserializer.BasicDeserialize(String input, Int32 depthLimit, JavaScriptSerializer serializer)
      at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize(JavaScriptSerializer serializer, String input, Type type, Int32 depthLimit)
      at System.Web.Script.Serialization.JavaScriptSerializer.Deserialize[T](String input)
      at ScriptMain.GetWebServiceResult(String wUrl)
      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()

      Is there somehow that i can contact you pr email ?

      Delete
    3. It seems that i somehow need to send this Header with the GET request

      Accept: application/json

      Delete
    4. Fixed that part by adding

      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      httpWReq.Accept = "application/json";

      Delete
    5. Somehow that i can change the script to show proper date ?

      Cost Code Date
      1056 401 /Date(1451689200000+0100)/

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

      Delete
    7. Hi Jim
      I have send you an email with the issues that i'm facing. Appreciate your time

      Delete
  58. Hi Jim,

    This is my JSON:
    {
    "spatialReference": {
    "wkid": 4269,
    "latestWkid": 4269
    },
    "candidates": [
    {
    "address": "1000 WEST WASHINGTON BOULEVARD, CHICAGO, 60607",
    "location": {
    "x": -87.652661691793284,
    "y": 41.883335595575815
    },
    "score": 89.040000000000006,
    "attributes": {

    }
    }
    ]
    }

    Here is the error: Foreach statement cannot operate on variables of type 'Location' because it does not contain a public definition for 'GetEnumerator'

    ReplyDelete
    Replies
    1. Can you help edit this script?
      Script:

      RootObject outPutMetrics = getWebServiceResult(wUrl);
      foreach (var metric in outPutMetrics.candidates)
      {
      Output0Buffer.AddRow();
      Output0Buffer.ADDRESS = metric.address;
      Output0Buffer.SCORE = metric.score;

      foreach (Location loc in metric.location)
      {
      Output1Buffer.AddRow();
      Output1Buffer.X = loc.x;
      Output1Buffer.Y = loc.y;
      }
      }
      }
      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
      {

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

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

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

      JavaScriptSerializer sr = new JavaScriptSerializer();
      jsonResponse = sr.Deserialize(jsonString);

      }

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

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

      }

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

      #region JSON Class
      public class SpatialReference
      {
      public int wkid { get; set; }
      public int latestWkid { get; set; }
      }

      public class Location
      {
      public double x { get; set; }
      public double y { get; set; }
      }

      public class Attributes
      {
      }

      public class Candidate
      {
      public string address { get; set; }
      public Location location { get; set; }
      public double score { get; set; }
      public Attributes attributes { get; set; }
      }

      public class RootObject
      {
      public SpatialReference spatialReference { get; set; }
      public List candidates { get; set; }
      }
      #endregion

      Delete
    2. What email address should I send to?

      Delete
    3. Hi Jennifer.

      The reason you are getting this error is because you are attempting to iterate through an object (Location) that is not a collection, i.e. is not something like a list or an array. Simply change:
      foreach (Location loc in metric.location)
      {
      Output1Buffer.AddRow();
      Output1Buffer.X = loc.x;
      Output1Buffer.Y = loc.y;
      }

      to

      Output1Buffer.AddRow();
      Output1Buffer.X = metric.location.x;
      Output1Buffer.Y = metric.location.y;

      But...given the structure of your data, I would just add these attributes to Output0Buffer and get rid of Output1Buffer. Hope this helps.

      Delete
  59. Hi David
    Is there somehow you can mail your script. i have same issue with limitations in the API

    ReplyDelete
  60. You are Awesome JIM bro :)
    Thanks a lot :)

    ReplyDelete
  61. Wonderful. Your article was so much help! Thank you!

    ReplyDelete
  62. Hi Jim,

    I am getting an error that foreach statement cannot operate on variables of type Listing because Listing does not contain a public definition or GetEnumerator. My code is to long to paste and would like to send it to you another way.
    Thanks

    ReplyDelete
  63. Hello Jim,

    thank you for sharing! I implemented the same but the JSON link will return around 200k records. So I was thinking that may be better to save it to a file and the parse it through SSIS. Have you implemented something similar or do you now what I tasks I have to use to implement it?

    Kind Regards,
    Angelos

    ReplyDelete
    Replies
    1. Does your API support pagination? This way you can pull down a set amount at a time and loop through the records until you process all 200k. If you wanted to just download the data to a file, that is also an option. The code for connecting to the web service wont be needed and you will instead have to use a file stream. A good example is: http://stackoverflow.com/questions/13297563/read-and-parse-a-json-file-in-c-sharp

      Delete
    2. Thank you for your response. That works with limit and offset (set on the url). Do you have any other example as my C# skills are pretty limited)?I have build already the classes and the for each statement for the fields.

      Delete
    3. It probably won't work the same way. But I do have an example here of pagination here: http://dennysjymbo.blogspot.com/2013/11/using-ssis-to-consume-data-from.html

      Delete
  64. Hi Jim - how to make a post request for batches of 1000 records? Thanks in advance.

    ReplyDelete
  65. Hi Jim,

    Your posting has helped me a lot. But as I'm pretty new to C# I've not been able to get a nested element in my output. Despite all similar questions asked above...

    My JSON:

    [
    {
    "id": "9039e307-0693-446c-9cd9-d5a17682e9d1",
    "name": "05.101",
    "branch": {
    "id": "cfb120fe-d286-486b-a980-f9c25e9f7bb8",
    "name": "Riethil"
    }
    },
    ]

    ReplyDelete
  66. My code:

    #region Namespaces

    #region Class

    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
    /// Zet de records in de output buffer
    public override void CreateNewOutputRows()
    {
    //Ophalen SSIS Variables
    string API_Server = Variables.TDWebDavServer;

    //Stel webservice URL samen
    string wUrl = "https://" + API_Server + "/tas/api/locations";
    //System.Windows.Forms.MessageBox.Show(wUrl);

    try
    {
    //Roep de webservice aan en geef de array "Locaties" terug
    RootObject[] outPutMetrics = GetWebServiceResult(wUrl);

    //Geef de output terug voor iedere groep van metrics
    foreach (var metric in outPutMetrics)
    {
    Output0Buffer.AddRow();
    Output0Buffer.LocatieId = metric.id;
    Output0Buffer.LocatieOmschrijving = metric.name;
    }

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

    }

    JSON
    private RootObject[] GetWebServiceResult(string wUrl)
    {
    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    httpWReq.Accept = "application/json";
    httpWReq.Headers["Authorization"] = "TOKEN id=\"" + Variables.APIToken + "\"";
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    RootObject[] jsonResponse = null;

    try
    {
    //Test de connectie
    if (httpWResp.StatusCode == HttpStatusCode.OK)
    {

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

    //Zet jsonString door gebruik te maken van een stream reader
    using (StreamReader reader = new StreamReader(responseStream))
    {
    jsonString = reader.ReadToEnd().Replace("\\", "");
    reader.Close();
    }

    //Deserialize de JSON
    JavaScriptSerializer sr = new JavaScriptSerializer();
    //JSON bevat een leading en trailing " die verwijderd moet worden
    jsonResponse = sr.Deserialize(jsonString.Trim('"'));

    }
    //Output connection fout boodschap
    else
    {
    FailComponent(httpWResp.StatusCode.ToString());

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

    }

    ///
    /// Fout boodschap weergeven
    ///
    private void FailComponent(string errorMsg)
    {
    bool fail = false;
    IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;
    compMetadata.FireError(1, "Fout bij het ophalen van data via de webservice!", errorMsg, "", 0, out fail);

    }

    }
    #endregion

    #region JSON Class
    //Class om de metrics van "Locaties" te bepalen
    //Deze kun je eenvoudig maken via http://json2csharp.com/

    public class Branch
    {
    public string id { get; set; }
    public string name { get; set; }
    }

    public class RootObject
    {
    public string id { get; set; }
    public string name { get; set; }
    public Branch branch { get; set; }
    }

    #endregion

    ReplyDelete
    Replies
    1. Sorry for the posting in three parts, was too long...

      So far I only get back the name and id from the root (e.g. "05.101" and "9039e307-0693-446c-9cd9-d5a17682e9d1"). I would also like to receive the branch id ("cfb120fe-d286-486b-a980-f9c25e9f7bb8").

      How would I do that? Many thanks in advance!

      Cheers, Erik

      Delete
    2. Have you tried something like:

      {
      Output0Buffer.AddRow();
      Output0Buffer.LocatieId = metric.id;
      Output0Buffer.LocatieOmschrijving = metric.name;
      Output0Buffer.BranchID = metric.branch.id;
      }

      Delete
    3. Yes! So simple! You're the man! Thx!

      Delete
    4. Hey Jim,

      My project is almost due for production, due to your help. Thanks again!
      I'm still struggling with one issue though..

      My class is as follows:

      public class Location
      {
      public string id { get; set; }
      }

      public class RootObject
      {
      public string id { get; set; }
      public string employeeNumber { get; set; }
      public string phoneNumber { get; set; }
      public string mobileNumber { get; set; }
      public Location location { get; set; }
      public Department department { get; set; }
      }

      public class Department
      {
      public string id { get; set; }
      }

      In the JSON the department is supplied like for example:

      "department": {

      "id": "da6b3fc2-4e10-4446-9ec0-7dc33b766693",

      "name": "ONT/MM"

      },

      So far so good. But in some cases there is no department and the JSON shows this:

      "department": null,

      In such cases the package fails with: "Object reference not set to an instance of an Object"
      Of course the reason for this is the missing department. But being very much a beginner in C# I've been struggling to find a solution. After many hours I've found none. For testing purposes I've just disabled the error handling but this is no real option.

      Do you have any suggestion on solving this? Many thanks in advance!

      Cheers,
      Erik

      Delete
    5. What line of code is it failing on?

      Delete
    6. Here is part of my code:

      public override void CreateNewOutputRows()
      {
      //Stel webservice URL samen
      //string wUrl = "https://" + Variables.TDSaaSServer + "/tas/api/persons/id/" + Variables.PersonId;
      //Test URL met eigen data
      string wUrl = "https://gemeentebreda-test.topdesk.net/tas/api/persons/id/4eedc0ab-bcf3-4903-8b98-7ad103f75071";
      //MessageBox.Show(wUrl);

      try
      {
      RootObject outPutMetrics = GetWebServiceResult(wUrl);
      Output0Buffer.AddRow();
      Output0Buffer.Id = Variables.PersonId;
      Output0Buffer.EmployeeNumber = outPutMetrics.employeeNumber;
      Output0Buffer.MobileNumber = outPutMetrics.mobileNumber;
      Output0Buffer.PhoneNumber = outPutMetrics.phoneNumber;
      Output0Buffer.LocationId = outPutMetrics.location.id;
      Output0Buffer.DepartmentId = outPutMetrics.department.id;

      }
      catch (Exception e)
      {
      //MessageBox.Show(wUrl);
      //Error handling staat tijdelijk uit omdat de import vastloopt op medewerkers zonder locatie of afdeling
      FailComponent(e.ToString());
      }



      In case a department is missing if fails on row:

      "Output0Buffer.DepartmentId = outPutMetrics.department.id;"

      Strangely the data gets pulled in correctly (with the department as NULL) and written to the database. The dataflow still fails however on the "Object reference not set to an instance of an object" error

      Delete
    7. Try replacing: Output0Buffer.DepartmentId = outPutMetrics.department.id;

      with

      if( outPutMetrics.department != null)
      Output0Buffer.DepartmentId = outPutMetrics.department.id;
      }

      Delete
    8. Works perfectly!! I've been really trying with this kind of IS NOT NULL constructs but did not get it working obviously... Thanks a lot again!!!!

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

    ReplyDelete
  68. HiJim,

    Nice Blog !! Once question, How can we pass input parameters to the web service ?

    Tahnks,
    Saurabh

    ReplyDelete
    Replies
    1. I would look at this link: https://social.technet.microsoft.com/wiki/contents/articles/22198.use-ssis-variables-and-parameters-in-a-script-component.aspx Then I would refer to your api documentation on how to pass variables. If its REST, it may be as easy as concatenating these passed in variables to your url string

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

      Delete
    3. Have you been able to pass variables into the C# environment from SSIS? Does this help?
      http://stackoverflow.com/questions/4015324/http-request-with-post

      Do you have any API documentation that you can point to?

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

      Delete
    5. How are you getting this output? How are you able to derive what the parameters are?

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

      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. This line is probably the problem.
      jsonResponse = sr.Deserialize< RootObject []>(jsonString.Trim('"'));
      change to

      jsonResponse = sr.Deserialize< RootObject >(jsonString.Trim('"'));

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

      Delete
    12. Issues Resolved. Your article was so much help!
      Many Thanks Again :)

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

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

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

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

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

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

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

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

      Delete
    8. Have you put a breakpoint after string wUrl = "https://dellsvcsua.service-now.com/api/now/table/sys_user?sysparm_limit=" + max ; and verified wUrl pasted in a web browser bring up a JSON doc?

      Delete
  70. In one of my fields is HTML tags and it is breaking the code. How do I remove it?

    ReplyDelete
    Replies
    1. inbound or outbound? Can you be more specific?

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

    ReplyDelete
    Replies
    1. You want to call the API in parallel? Or do you want to loop through it synchronously until you get all the records? Can you make use of pagination?

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

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

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

      Delete
    5. Hey man do you still need help? Sorry about the late response, I have been working a lot of overtime lately and haven't had much time to work on the blog.

      Delete
  72. Hi, very useful, still struggling with posting to API using JSON. You wouldn't happen to have a blog on that?

    Thanks in advance

    ReplyDelete
  73. Hi Jim,
    My JSON is not working with this Code. Can you please help?

    {"request":{"command":"series","series_id":"PET_IMPORTS.WORLD-US-ALL.M"},"series":[{"series_id":"PET_IMPORTS.WORLD-US-ALL.M","name":"Imports of all grades of crude oil from World to Total U.S. (US), Monthly","units":"thousand barrels","f":"M","copyright":"None","source":"EIA, U.S. Energy Information Administration","geography":"WLD","geography2":"USA","start":"200901","end":"201705","updated":"2017-07-31T13:55:30-0400","data":[["201705",260322],["201704",243922],["201703",249501],["201702",220906],["201701",261497],["201612",243542],["201611",241606],["201610",235827],["201609",241721],["201608",249099],["201607",250845],["201606",228320],["201605",246323],["201604",229100],["201603",249300],["201602",229402],["201601",237910],["201512",244965],["201511",221130],["201510",220171],["201509",216838],["201508",239212],["201507",228160],["201506",219618],["201505",224604],["201504",216229],["201503",235360],["201502",198807],["201501",222315],["201412",223978],["201411",218845],["201410",221600],["201409",224851],["201408",231649],["201407",236534],["201406",212025],["201405",222177],["201404",226639],["201403",225487],["201402",201572],["201401",234969],["201312",240519],["201311",222248],["201310",230822],["201309",237699],["201308",251079],["201307",245724],["201306",231912],["201305",238809],["201304",231621],["201303",231414],["201302",203162],["201301",245715],["201212",235120],["201211",244987],["201210",250303],["201209",250160],["201208",263945],["201207",266782],["201206",270684],["201205",272766],["201204",258417],["201203",271697],["201202",242560],["201201",257536],["201112",279359],["201111",268189],["201110",284594],["201109",273698],["201108",287633],["201107",295338],["201106",285572],["201105",289106],["201104",265822],["201103",292010],["201102",235122],["201101",296158],["201012",279096],["201011",271185],["201010",272415],["201009",284192],["201008",309053],["201007",318991],["201006",310153],["201005",310396],["201004",302836],["201003",299033],["201002",253150],["201001",274568],["200912",265615],["200911",273248],["200910",272678],["200909",289561],["200908",279111],["200907",287569],["200906",284093],["200905",281147],["200904",285934],["200903",303897],["200902",262339],["200901",317275]]}]}

    ReplyDelete
  74. Hi Jim I have a URL with the authentication info .
    {"auth":"*****","status":"success"}
    I am trying to get that so that i could use authentication token to call other API like below

    {"monitors":[{"id":712,"name":"Team 12","description":"Team 12","type":"BUZZ","enabled":true,"resultsStart":"2017-07-26T00:00:00","resultsEnd":"2017-08-16T00:00:00","keywords":"Trump","languages":{"values":[],"type":"include"},"geolocations":{"values":[],"type":"include"},"gender":"","sources":["Twitter","Google Plus","Facebook","Blogs","Comments","Forums","Tumblr","Instagram"],"timezone":"New_York","teamName":"Summit","tags":[],"subfilters":[]},
    {"id":710,"name":"team2","description":"team2","type":"BUZZ","enabled":false,"resultsStart":"2017-07-01T00:00:00","resultsEnd":"2017-08-02T00:00:00","keywords":"EY","languages":{"values":["en"],"type":"include"},"geolocations":{"values":["AUS"],"type":"include"},"gender":"","sources":["Twitter","Facebook","Blogs","Comments","Forums","Instagram"],"timezone":"New_York","teamName":"Summit","tags":[],"subfilters":[]},
    {"id":709,"name":"Team 1","description":"Team 1","type":"BUZZ","enabled":false,"resultsStart":"2017-08-01T00:00:00","resultsEnd":"2017-08-16T00:00:00","keywords":"noodles","languages":{"values":["ar"],"type":"include"},"geolocations":{"values":[],"type":"include"},"gender":"","sources":["News"],"timezone":"New_York","teamName":"Summit","tags":[],"subfilters":[]}

    ReplyDelete
  75. I am not dotnet proficient. Please see the code below

    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using System.Net;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    using System.Web.Script.Serialization;
    #endregion


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



    public override void CreateNewOutputRows()
    {
    string wUrl = "authenticationurlwithusername and password with a property set to true";


    try
    {
    //Call getWebServiceResult to return our WorkGroupMetric array
    WorkGroupMetric[] outPutMetrics = GetWebServiceResult(wUrl);

    //For each group of metrics output records
    foreach (var metric in outPutMetrics)
    {
    Output0Buffer.AddRow();
    Output0Buffer.auth = metric.auth;
    Output0Buffer.status = metric.status;
    }

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

    //HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    //HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();

    //if (httpWResp.StatusCode == HttpStatusCode.OK)
    //{
    // MessageBox.Show("connectionsdone");
    //}

    }

    private WorkGroupMetric[] GetWebServiceResult(string wUrl)
    {

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

    try
    {
    //Test the connection
    if (httpWResp.StatusCode == HttpStatusCode.OK)
    {

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

    //Set jsonString using a stream reader
    using (StreamReader reader = new StreamReader(responseStream))
    {
    jsonString = reader.ReadToEnd().Replace("\\", "");
    reader.Close();
    }

    //Deserialize our JSON
    JavaScriptSerializer sr = new JavaScriptSerializer();
    //JSON string comes in with a leading and trailing " that need to be removed for parsing to work correctly
    //The JSON here is serialized weird, normally you would not need this trim
    jsonResponse = sr.Deserialize(jsonString.Trim('"'));

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

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

    }


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

    }

    }
    #endregion


    #region JSON Class
    //Class to hold our work group metrics
    class WorkGroupMetric
    {
    public string auth { get; set; }

    public string status { get; set; }


    }
    #endregion

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Sorry I have never used tokens...are you still struggling with this?

      Delete
  76. First off, this post is great. Thanks for the time in putting this together.
    I did run into an issue that I'm not sure how to deal with. The API is returning a different result if the provider is no longer in service.
    https://npiregistry.cms.hhs.gov/api/?number=1245405612
    Compared to
    https://npiregistry.cms.hhs.gov/api/?number=1134271711
    that works.
    Is there a way to ignore the response when the JSON is in a different format?

    ReplyDelete
  77. correct, the first link is an example of one causing an error. The second link is a sample of what I'm getting 99% of the time and works fine. I'm reading a list of a few hundred NPI numbers from a database where I need to return updated information for the Doctors office. When I hit a number that is no longer valid I get the error. I'm not sure how to ignore the error.

    ReplyDelete
  78. Can you do a try catch for this? Catch the exception instead of throwing it?

    ReplyDelete
    Replies
    1. Thanks for the help Jim. Sometimes the right question is all I need. I got it working 100%.
      I just made the catch empty. Not best practices, but works for me in this scenario.
      catch (Exception e) {}
      For anyone else who needs NPI data use the layout to know what fields are possible in the json response. The json2csharp converter only shows fields that are returned for that one query. NPI-1 and NPI-2 data are different so I suggest capturing everything.
      https://npiregistry.cms.hhs.gov/registry/Json-Conversion-Field-Map

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

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

    ReplyDelete
  81. You saved my time. fabulous job. Thank you.

    ReplyDelete
  82. Hi Jim, great article , fantastic job on this. I hope you are still replying to comments because I just have a question. After deserialize json, I am trying to print the array but it always returns rootobject. Any idea why?

    In your example above I am trying to print, “jsonresponse” . Thanks you in advance

    ReplyDelete
  83. Hi Jim,
    Quick question, Do I have to pass my Jira User Name and P.W through URL to Pull the data ?

    ReplyDelete
    Replies
    1. No idea, it might be in the header.

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

      Delete
  84. Hi Jim,
    Can you help me with the below JSON class?

    public class Fields
    {
    public string summary { get; set; }
    public List customfield_14804 { get; set; }
    public object customfield_17020 { get; set; }
    public double customfield_14802 { get; set; }
    public object customfield_17430 { get; set; }
    public object customfield_17414 { get; set; }
    public string customfield_17500 { get; set; }
    public object customfield_15524 { get; set; }
    }

    public class Issue
    {
    public string expand { get; set; }
    public string id { get; set; }
    public string self { get; set; }
    public string key { get; set; }
    public Fields fields { get; set; }
    }

    public class RootObject
    {
    public string expand { get; set; }
    public int startAt { get; set; }
    public int maxResults { get; set; }
    public int total { get; set; }
    public List issues { get; set; }
    }

    ReplyDelete