我是在 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; }
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)