Using a Script Component Source to Consume an XML Feed in SSIS

     When attempting to use XML as a data source in SSIS you really only have 2 solutions (and this depends on whether or not this XML is coming from a web service). If your XML feed is coming from a web service, you can use the Web Service Task and set the output to either a file or a variable. If you set your file to a variable, or file, you can use the XML Task to perform various xpath queries, reformat using an XSLT, etc. The result of which can be set to other variables or a file. If set to a file, it can then be sourced using an XML  Source  and brought into your data flow. But what if you don't want to use an XML file as your source? SSIS has no way of reading an XML feed from a web service directly into a data flow without custom coding. In this entry I'll show an example of how this can be done using a Script Component source in a data flow.

     This solution is very similar to the JSON solution I wrote about, with a few changes to apply to XML.  The XML source we're using is going to apply to metrics used in a call center. These attributes include employee login, date, calls offered, calls answered and calls missed. Our XML source will look something  like this:

<?xml version="1.0" encoding="utf-8"?>

<DisplayStats>

  <DailyStat>

    <WebLogin>eli</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>4</CallsOffered>

    <CallsAnswered>4</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>rit</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>0</CallsOffered>

    <CallsAnswered>0</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>Dan</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>15</CallsOffered>

    <CallsAnswered>15</CallsAnswered>

    <CallsMissed>1</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>Pab</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>0</CallsOffered>

    <CallsAnswered>0</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>Rus</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>21</CallsOffered>

    <CallsAnswered>21</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

</DisplayStats>

This comes from an ASP.NET web service I built using SOAP:

Figure 1. XML String

You can see from this screenshot that the web service takes one parameter, offerDate, with a date format. We're going to want to make this a variable that we can alter at run time every time this package is executed. This can be done through values returned from an Execute Sql Task from a database, set via a function, etc. Regardless of how its implemented, in this example were going to hard code the variable to 03/28/2013:

Figure 2. SSIS Variable

     Now that we have a variable for offerDate, let's set up our script component. Drag a script component onto the design surface of a data flow. When prompted, select source as the script component type:

Figure 3. Script Component Type

     On the script screen, we want to make sure to add our SSIS variable in ReadOnlyVariables:

Figure 4. Script Component Script Screen

     Next, let's configure the output buffer for this script component. On the input and output screen, click on the default output and add some columns. These will represent the XML elements were going to get back from our web service:

Figure 5. Input and Outputs Screen

    This screen helps us to define what our output is going to look like. The columns that will represent our XML elements are as follows: WEBLOGIN DT_STR 50,  DATE DT_DBTIMESTAMP, CALLSOFFERED DT_I4, CALLSANSWERED DT_I4 and CALLSMISSED DT_I4. With this all set up, we can go back to the script screen, click on the Edit Script button and begin coding. In the main.cs file, paste the following code:

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Xml.Serialization;

using System.Net;

using System.IO;

#endregion

#region Class

/// <summary>

/// This is the class to which to add your code.  Do not change the name, attributes, or parent

/// of this class.

/// </summary>

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    #region Methods

    /// <summary>Outputs records to the output buffer</summary>

    public override void CreateNewOutputRows()

    {

       //Get SSIS Variables

         string offerDate = Variables.offerDate;

     

        //Set Webservice URL

         string wUrl = "http://yourwebserviceurl/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());

        }

    }

    /// <summary>

    /// Method to call the web service and return our DailyStat array

   /// </summary>

   /// <param name="wUrl">The web servie URL</param>

   /// <returns>An array of DailyStat which contains the deserialized XML</returns>

    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;

    }

    /// <summary>

    /// Outputs an error message

    /// </summary>

    /// <param name="errorMsg">The error message</param>

    private void FailComponent(string errorMsg)

    {

        bool fail = false;

        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

        compMetadata.FireError(1, "Error Getting Data From Webservice!", errorMsg, "", 0, out fail);

    }

    #endregion

}

#endregion

#region XML Classes

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

}

#endregion

After calling our web service and getting our stream of XML, we deserialize into a DailyStats object(Creating the classes for your XML to deserialize into can be done by copying a sample of your XML into your clipboard then pasting special into a .net 4.5 project in VS2012 with edit -->paste special-->Paste XML as Classes):

                 //Deserialize our XML

                    XmlSerializer sr = new XmlSerializer(typeof(DailyStats));

                    xmlResponse = (DailyStats)sr.Deserialize(reader);

This object is returned to the calling method. We then loop through the DailyStat[] array, within the DailyStats object, and output the attributes to the output buffer:  

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

            }

     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:

Figure 6. Data Flow

Let's now run the package and view the results:

Figure 7. Data Viewer

The XML data is now in our data flow and can be sent to a data destination. No physical XML file was needed, which means we didn't have to rely on an XML Source Component to get this.