Importing a Json file using script task in SSIS

Question

I am new to importing API's using script task in SSIS. I have to manage the import of a straightforward API JSON file but this second API JSON file is a bit tricky. I have been looking at the code and just don't know what I'm doing wrong.

My JSON file has a header which I need to determine how many pages of the API I need to loop through to get the data, but where I'm lost is, how do you import the next bit of data. I think I am close but since I'm new to this I could do it with a bit of guidance.

My code in the script task below, the have seen the stream reader import the data but I do not what use (e.g List, class or dictionary)to import the countIn, countOut , etc columns.

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

            //For each group of metrics output records
            foreach (var metric in outPutMetrics)
            {
                APIBuffer.AddRow();
                APIBuffer.count = metric.count;
                APIBuffer.currentpage =  metric.currentpage;
                APIBuffer.totalpages = metric.totalpages;
                APIBuffer.countIn = metric.result.countIn;
                APIBuffer.countOut = metric.result.countOut;
                APIBuffer.type = metric.result.type;
                APIBuffer.countLine = metric.result.countLine;
                APIBuffer.from = metric.result.from;
                APIBuffer.to = metric.result.to;
            }

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

    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;

        throw new NotImplementedException();
    }

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

    }
}
//Class to hold our workgroup metrics
class WorkGroupMetric
{
    public string count { get; set; }
    public string currentpage { get; set; }
    public string totalpages  { get; set; }
    public List<Result> result { get; set; }   

}

class Result
{
    public string countIn { get; set; }
    public string countOut { get; set; }
    public string type { get; set; }
    public string countLine { get; set; }
    public string from { get; set; }
    public string to { get; set; }

}

If your SSIS package is 80% C# code, SSIS is not the right tool. Anyway, by _how do you import the next bit of data_ do you mean how to you call the next page, or how do you access the data in the `results` array? — Nick.Mc, Jan 08 '21 at 02:44
Does your code work as-is? have you tried looping through the `metric.results`? Does it work or do you get an error? — Nick.Mc, Jan 08 '21 at 06:22
Hi Nick, Thanks for responding. This project is for my work and they are using SSIS atm, out of curiosity what would be the right Microsoft tool for this as they Microsoft heavy at the moment. - Kiran — user11034054, Jan 08 '21 at 20:36
So the errors im getting are, with the `public List result { get; set; } ` line. error being `List' does not contain a definition for 'countIn' and no accessible extension method 'countIn' accepting a first argument of type 'List' could be found (are you missing a using directive or an assembly reference?` — user11034054, Jan 08 '21 at 20:41
Then I change `public List result { get; set; }` to `public Result result { get; set; }` the code is happier but then I get an error message in line `foreach (var metric in outPutMetrics)` saying `System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object.` Im clueless, i do know that its to with json file data hierarchy just don't how to solve it. — user11034054, Jan 08 '21 at 20:44
Another platform option would be Powershell which is the native windows scripting language but that won't solve your immediate problem. I'm going to add a C# tag to this because this is really a C# question which I can't answer easily. — Nick.Mc, Jan 09 '21 at 02:44
Here is an example of building C# classes to store JSON that contains a nested array (as in your case). In your JSON sample, everything between `[` and `]` is an array. https://www.tomasvera.com/programming/using-javascriptserializer-to-parse-json-objects/. Note that: 1. The JSON array data is represented in the class as an array also. So I think you should be using an array not a `List`. This is also where I come unstuck in C# - knowing which types of collections to use and how to cast them. — Nick.Mc, Jan 09 '21 at 02:46

score 1 · Answer 1 · answered Jan 19 '21 at 17:50

So I have got the code working, posting it so t can help someone in the future.

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

            //For each group of metrics output records
            //foreach (var metric in outPutMetrics)
            //{


            var ts = outPutMetrics.results;

            totalcount = Int32.Parse(outPutMetrics.count);

            foreach (var a in ts)
            {
                Output0Buffer.AddRow();
                //Output0Buffer.count = outPutMetrics.count;
                
                Output0Buffer.countIn = a.countIn;
                Output0Buffer.countOut = a.countOut;
                Output0Buffer.type = a.type;
                Output0Buffer.countLine = a.countLine;
                Output0Buffer.from = a.from;
                Output0Buffer.to = a.to;

                i = i + 1;
            }

            Console.Write(i);
        }
        catch (Exception e)
        {
            FailComponent(e.ToString());
        }
    }

    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;

        throw new NotImplementedException();
    }

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

    }
}
//Class to hold our work group metrics
class WorkGroupMetric
{
    public string count { get; set; }
    public string currentpage { get; set; }
    public string totalpages { get; set; }
    public Result[] results { get; set; }

}

class Result
{
    public string countIn { get; set; }
    public string countOut { get; set; }
    public string type { get; set; }
    public string countLine `enter code here`{ get; set; }
    public string from { get; set; }
    public string to { get; set; }

}

Thanks for posting the complete code. I see that all of my recommendations were followed. — Nick.Mc, Jan 29 '21 at 05:18

score 0 · Answer 2 · answered Jan 09 '21 at 02:50

Going by this example

https://www.tomasvera.com/programming/using-javascriptserializer-to-parse-json-objects/

Possibly this should be your C# class definition, using an array not a List

class WorkGroupMetric
{
    public string count { get; set; }
    public string currentpage { get; set; }
    public string totalpages  { get; set; }
    public Result[] results { get; set; }   

}

class Result
{
    public string countIn { get; set; }
    public string countOut { get; set; }
    public string type { get; set; }
    public string countLine { get; set; }
    public string from { get; set; }
    public string to { get; set; }

}

Also you're only getting one WorkGroupMetric per call, not an array of them so you should probably be using this:

WorkGroupMetric

not this

WorkGroupMetric[]

throughout

Hi Nick, Thank you so much for you help, it hasn't solved my problems but it has definitely pointed into the right direction in finding an answer. — user11034054, Jan 12 '21 at 18:10
Good stuff. If you come up with an answer, it's great to post it here to help others (including me!) — Nick.Mc, Jan 12 '21 at 22:47

Importing a Json file using script task in SSIS

2 Answers2

Linked