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.Xml.Serialization;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void CreateNewOutputRows()
{
//Get SSIS Variables
string offerDate = Variables.OFFERDATE;
//Set Webservice URL
string wUrl = “
http://YOUR WEB SERVICE URI/Respiratory/ResService.asmx/getAgentMetricsXml?offerDate=”+offerDate;
try
{
//Call getWebServiceResult to return our DailyStat array
DailyStats outPutStats = getWebServiceResult(wUrl);
//For each group of metrics output records
foreach (var stats in outPutStats.MDailyStat)
{
Output0Buffer.AddRow();
Output0Buffer.WEBLOGIN = stats.WebLogin;
Output0Buffer.DATE =stats.Date;
Output0Buffer.CALLSANSWERED =stats.CallsAnswered;
Output0Buffer.CALLSOFFERED = stats.CallsOffered;
Output0Buffer.CALLSMISSED = stats.CallsMissed;
}
}
catch (Exception e)
{
failComponent(e.ToString());
}
}
//Method to return our DailyStat array
private DailyStats getWebServiceResult(string wUrl)
{
HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
DailyStats xmlResponse = null;
try
{
//Test the connection
if (httpWResp.StatusCode == HttpStatusCode.OK)
{
Stream responseStream = httpWResp.GetResponseStream();
//Set xmlString using a stream reader
using (StreamReader reader = new StreamReader(responseStream))
{
//Deserialize our XML
XmlSerializer sr = new XmlSerializer(typeof(DailyStats));
xmlResponse = (DailyStats)sr.Deserialize(reader);
reader.Close();
}
}
//Output connection error message
else
{
failComponent(httpWResp.StatusCode.ToString());
}
}
//Output xml parsing error
catch (Exception e)
{
failComponent(e.ToString());
}
return xmlResponse;
}
//Outputs error message
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 elements within <DailyStat>
public class DailyStat
{
public string WebLogin { get; set; }
public DateTime Date { get; set; }
public int CallsOffered { get; set; }
public int CallsAnswered { get; set; }
public int CallsMissed { get; set; }
}
//Class to hold our array of <DailyStat>
[XmlRootAttribute(“DisplayStats”)]
public class DailyStats
{
[XmlElement(“DailyStat”)]
public DailyStat[] MDailyStat { get; set; }
}
After calling our web service and getting our stream of XML, we deserialize into a DisplayStats object:
This object is returned to the calling method. We then loop through the DailyStat[]array, within the DisplayStats object, and output the attributes to the output buffer:
Build, save and lets return to the data flow. We can create a union all that the script component can output to so we can add a data viewer to see the records. In a normal implementation this would go to a data destination: