在SSIS中使用脚本任务导入Json文件

我是在 SSIS 中使用脚本任务导入 API 的新手。 我必须管理一个简单的 API JSON 文件的导入，但这第二个 API JSON 文件有点棘手。我一直在看代码，只是不知道我做错了什么。

我的 JSON 文件有一个标头，我需要确定需要循环访问多少页 API 才能获取数据，但我迷失的是如何导入下一位数据。我想我已经很接近了，但由于我是新手，我可以在一些指导下做到这一点。

我在下面的脚本任务中的代码，已经看到流读取器导入数据，但我不知道使用什么（例如列表、类或字典）来导入countIn, countOut等栏目。

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

}

所以我已经让代码正常工作了，将其发布以便将来可以帮助别人。

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

}