Object reference not set to an instance of an object in deserialize the json data

Question

I am getting "Object reference not set to an instance of an object" error while deserialize the JSON Array Data.

Below is code used in script task in SSIS.

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
 {
  private long dataLength;

  public override void PreExecute()
   {
    base.PreExecute();
   }

public override void PostExecute()
{
    base.PostExecute();
}

public override void CreateNewOutputRows()
{

    //Get SSIS Variables
    string apiUserName = Variables.APIUsername;
    string apiPassword = Variables.APIPassword;
    //int campaignId = (int)Variables.Campaign;

    //Set Webservice URL
    string wUrl = "My URL Here";
    string base64 = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(apiUserName + ":" + apiPassword));
    try
    {
        //Call getWebServiceResult to return our WorkGroupMetric array
        Root outPutMetrics = GetWebServiceResult(wUrl, base64);

        //For each group of metrics output records
        //these are the column names defined in output columns in object
        foreach (var metric in outPutMetrics.Rows)
        {
            Output0Buffer.AddRow();

           Output0Buffer.EmployeeReport = metric.EmployeeReport;
            Output0Buffer.EmployeeDisplayName = metric.Employee_DisplayName;
            Output0Buffer.PRPayRunResultPermanentEmployeeNumber = metric.PRPayRunResultPermanent_EmployeeNumber;
            Output0Buffer.OrgUnitShortName = metric.OrgUnit_ShortName;
          

        }

    }
    catch (Exception e)
    {
        //FailComponent(e.ToString());
        if (e.Message != null)
        {
            string ExceptionMessage = e.Message;

        }

    }

}

/// <returns>An array of WorkGroupMetric composed of the de-serialized JSON</returns>
private Root GetWebServiceResult(string wUrl, string base64)
{

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

    httpWReq.Headers.Add("Authorization", "Basic " + base64);
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    Root jsonResponse = null;

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

            Stream responseStream = httpWResp.GetResponseStream();
             dataLength = httpWResp.ContentLength;

            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();
            sr.MaxJsonLength=2147483644;
            jsonResponse = sr.Deserialize<Root>(jsonString);
            

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

        }
    }
    //Output JSON parsing error
    catch (Exception ex)
    {
        if (ex.Message != null)
        {
            string ExceptionMessage = ex.Message;

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

}

}

Below are C# classes from JSON2CSHARP.com

public class Row
{
public string Employee_DisplayName { get; set; }
public string PRPayRunResultPermanent_EmployeeNumber { get; set; }
public string OrgUnit_ShortName { get; set; }

public string EmployeeReport{ get; internal set; }
  }

   public class Data
   {
     public string EmployeeReport{ get; set; }
     public List<Row> Rows { get; set; }
     }



    public class Root
     {

      public List<Row> Rows { get; set; }
      public Data data { get; set; }

       }

Could you please help me to resolved the issue as soon as possible. jsonResponse returns the data from Data and and Contains the data. But after running foreach loop it is through Object reference not set to an instance of an object error.

You should be deserializing with `Root` not `Data` so it should be `sr.Deserialize(jsonString);` — Franck, Oct 26 '20 at 15:15
your JSON is invalid "Plant 2 - Assembly 1"- Assembly 1" you have for some reason closing quotations — styx, Oct 26 '20 at 15:16
Whenever you're dealing with JSON and you're having errors, a good first step is to make sure it's valid JSON, yours is not. There are plenty of JSON validators, use them! — Lou O., Oct 26 '20 at 15:21
Hi Franck, I tried to deserialize with Root. But it didnot worked for me — Narasimha Reddy Valasammagari, Oct 26 '20 at 15:36

score 0 · Answer 1 · answered Oct 26 '20 at 15:58

The code does work if you use Root instead of Data. Paste the code below into a Console App and it will output the data correctly:

class Program
{
    static void Main(string[] args)
    {
        var jsonString = "{\"Data\":{\"EmployeeReport\":\"Payroll_Earning_Hours_Detail\",\"Rows\":[{\"Employee_DisplayName\":\"Narasimha Reddy\",\"Permanent_EmployeeNumber\":\"8965594\",\"OrgUnit_ShortName\":\"Plant 2 - Assembly 1\"}]}}";
        Console.WriteLine(Regex.Unescape(jsonString));
        JavaScriptSerializer sr = new JavaScriptSerializer();
        sr.MaxJsonLength = 2147483644;
        Root jsonResponse = sr.Deserialize<Root>(jsonString);
        Console.WriteLine(jsonResponse.Data.EmployeeReport);  // Payroll_Earning_Hours_Detail
        Console.WriteLine(jsonResponse.Data.Rows[0].Permanent_EmployeeNumber);  // 8965594
        Console.ReadLine();
    }
}

public class Row
{
    public string Employee_DisplayName { get; set; }
    public string Permanent_EmployeeNumber { get; set; }
    public string OrgUnit_ShortName { get; set; }
}

public class Data
{
    public string EmployeeReport { get; set; }
    public List<Row> Rows { get; set; }
}

public class Root
{
    public Data Data { get; set; }

}

Thanks Rich N, I tried its working fine in console application. But i am trying to insert json data into sql table. — Narasimha Reddy Valasammagari, Oct 27 '20 at 11:23
OK. In that case for us to help you you need to show us what the problem is in more detail, don't you? Perhaps you can edit the question to show what's going wrong? At the moment we've got some code that seems to work! — Rich N, Oct 27 '20 at 11:48
Hi Rich N, I edited my question with current issue. Could you help me on it. — Narasimha Reddy Valasammagari, Oct 27 '20 at 14:23
What you need to do here is to debug the code and try to work out what's happening yourself. There isn't enough information here for us to be able to tell. I can't see anything obviously wrong, and we can't recreate the problem without access to the data. The JSON you showed earlier won't throw an exception in this code, but my guess is there's other JSON that will. — Rich N, Oct 27 '20 at 18:50

Object reference not set to an instance of an object in deserialize the json data

1 Answers1