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
– Nick.Mc, Commented Jan 8, 2021 at 2: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
– Nick.Mc, Commented Jan 8, 2021 at 6: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
– user11034054, Commented Jan 8, 2021 at 20:36
So the errors im getting are, with the public List<Result> result { get; set; } line. error being List<Result>' does not contain a definition for 'countIn' and no accessible extension method 'countIn' accepting a first argument of type 'List<Result>' could be found (are you missing a using directive or an assembly reference? — user11034054
– user11034054, Commented Jan 8, 2021 at 20:41
Then I change public List<Result> 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
– user11034054, Commented Jan 8, 2021 at 20:44

user11034054 · Accepted Answer · 2021-01-19 17:50:59Z

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 · Accepted Answer · 2021-01-09 02:50:17Z

0

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

answered Jan 9, 2021 at 2:50

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

2 Comments

user11034054 Over a year ago

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.

Nick.Mc Over a year ago

Good stuff. If you come up with an answer, it's great to post it here to help others (including me!)

Collectives™ on Stack Overflow

Importing a Json file using script task in SSIS

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related