Tuesday, May 21, 2013

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.

78 comments:

  1. Hi,,

    Can you please let me know where i have to put this code:

    //After calling our web service and getting our stream of XML, we deserialize into a DisplayStats object:



    System.Xml.XmlSerialization.XmlSerializer sr = new XmlSerializer(typeof(OUTPUT));
    xmlResponse = (DisplayStats)sr.Deserialize(reader);

    //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:
    //Call getWebServiceResult to return our DailyStat array
    OUTPUT 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.Unsub = stats.Unsub;
    Output0Buffer.Abuse = stats.Abuse;
    //Output0Buffer.CALLSMISSED = stats.CallsMissed;
    }

    I am new to c#,
    Pls help me out

    ReplyDelete
  2. You do all of your coding in the script component. Can you show me all of your code so far? Can you show me a sample of the XML your'e dealing with?

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi Jim.

    I am getting following errors:

    Error 1 A namespace does not directly contain members such as fields or methods
    Error 2 Expected class, delegate, enum, interface, or struct

    Xml:

    success


    2013-06-12
    477
    23


    ReplyDelete
  5. I think I can see what the issue is....you need to use the xml node names provided...not what I have in the example. My example uses the xml in this blog, you need to make your code match whatever your xml is. For instance, you are still WebLogin, is that in your xml? If you post a sample of the xml that is being returned from you web service I can help you debug this. You can change the values if you want, so it won't contain proprietary data, but I would like to see what you're trying to deserialize.

    ReplyDelete
  6. API is returning following :


    success


    2013-06-12
    477
    23


    ReplyDelete
  7. Nevermind...I see you have weblogin commented out, I still would like to see the xml please.

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. So your web service doesn't provide xml? If not then you shouldn't try to use XmlSerializer on simple text and would need a different solution.

    If it does then can you download Simple Rest Client (https://chrome.google.com/webstore/detail/simple-rest-client/fhjcajmcbmldlhcimfajhfbgofnpcjmb?hl=en)
    and tell me the format of the xml returned.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hi Jim,

    Can you please send me SSIS package you developed.

    My email id : maneeshhbtik@gmail.com

    ReplyDelete
  13. Does OUTPUT only occur once per DATASET or can it show up multiple times? I assume there can be multiple RECORD nodes in OUTPUT.

    ReplyDelete
  14. yes there can be multiple RECORD nodes in OUTPUT

    ReplyDelete
  15. //GOT IT!

    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()
    {

    //Set Webservice URL
    string wUrl = "https://api.extouch.com";
    try
    {

    //Call getWebServiceResult to return our DailyStat array
    DataSet outPutStats = getWebServiceResult(wUrl);


    //For each output
    foreach (var stats in outPutStats.MoutPut)
    {


    //For each group of metrics output records
    foreach (var records in stats.Record)
    {

    Output0Buffer.AddRow();
    Output0Buffer.Date = records.Date;
    Output0Buffer.Unsub = records.Unsub;
    Output0Buffer.Abuse = records.Abuse;

    }
    }

    }
    catch (Exception e)
    {
    failComponent(e.ToString());
    }


    }

    //Method to return our DailyStat array
    private DataSet getWebServiceResult(string wUrl)
    {

    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    DataSet xmlResponse = null;

    try
    {
    //Test the connection
    if (httpWResp.StatusCode == HttpStatusCode.OK)
    {

    Stream responseStream = httpWResp.GetResponseStream();
    MessageBox.Show("success");

    //Set xmlString using a stream reader
    using (StreamReader reader = new StreamReader(responseStream))
    {
    //Deserialize our XML
    XmlSerializer sr = new XmlSerializer(typeof(DataSet));
    xmlResponse = (DataSet)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);

    }


    }

    public class OUTPUT
    {
    [XmlElement("RECORD")]
    public RECORD[] Record { get; set; }
    }

    public class RECORD
    {
    public DateTime Date { get; set; }
    public int Unsub { get; set; }
    public int Abuse { get; set; }
    }

    //Class to hold our array of
    [XmlRootAttribute("DATASET")]
    public class DataSet
    {
    [XmlElement("OUTPUT")]
    public OUTPUT[] MoutPut { get; set; }

    [XmlElement("TYPE")]
    string type { get; set; }
    }

    ReplyDelete
  16. Hi Jim,

    Thanks a lot for your help!!

    Got the output.

    ReplyDelete
  17. No Problem man...good luck with your project.

    ReplyDelete
  18. Hi I am a bit lost (/I think I have similar problem to Maneesh -might not, total newbie here), what I am trying to do is get the data from here

    http://sfngroup.jobs/developer/jobs-in/feed/xml?num_items=100&offset=0

    so I guess my variables are num_items and offset

    But my build fails with:
    A namespace cannot directly contain members such as fields or methods
    error for the //Deserialize our XML part

    ReplyDelete
    Replies
    1. Ok, got this working. Was a little tricky because the paste XML as classes in .net 4.5 didn't like this XML. Anyways...here's how to get this working.

      1. Create 3 outputs on the Inputs and Outputs screen of the script component that contain this:

      source
      publisher DT_STR 50
      publisherurl DT_STR 100
      lastBuildDate DT_STR 100

      sourceJob
      country_short DT_STR 100
      city DT_STR 100
      description DT_TEXT
      date_new DT_STR 50
      country DT_STR 50
      company DT_STR 200
      title DT_STR 200
      state DT_STR 50
      reqid DT_STR 100
      state_short DT_STR 50
      location DT_STR 100
      guid DT_STR 100
      uid DT_STR 100
      url DT_STR 100

      sourceLink
      href DT_STR 100
      rel DT_STR 100

      Delete
    2. 2. Use this code in your script component:

      #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;
      using System.Text;
      #endregion

      #region Class

      [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
      public class ScriptMain : UserComponent
      {

      #region Methods
      public override void CreateNewOutputRows()
      {

      //Set Webservice URL
      string wUrl = "http://sfngroup.jobs/developer/jobs-in/feed/xml?num_items=100&offset=0";

      try
      {
      //Call getWebServiceResult to return our source object
      source outputSource = GetWebServiceResult(wUrl);

      //output source attributes
      sourceBuffer.AddRow();
      sourceBuffer.lastBuildDate=outputSource.lastBuildDate;
      sourceBuffer.publisher=outputSource.publisher;
      sourceBuffer.publisherurl=outputSource.publisherurl;


      ////For each link in source
      foreach (var links in outputSource.link)
      {
      sourceLinkBuffer.AddRow();
      sourceLinkBuffer.href = links.href;
      sourceLinkBuffer.rel = links.rel;
      }

      //For each job in source
      foreach (var jobs in outputSource.job)
      {
      sourceJobBuffer.AddRow();
      sourceJobBuffer.city=jobs.city;
      sourceJobBuffer.company=jobs.company;
      sourceJobBuffer.country=jobs.country;
      sourceJobBuffer.countryshort=jobs.country_short;
      sourceJobBuffer.datenew=jobs.date_new;

      if (jobs.description != null)
      {
      sourceJobBuffer.description.AddBlobData(Encoding.Unicode.GetBytes(jobs.description));
      }
      //Handle if text is null
      else
      {
      sourceJobBuffer.description.SetNull();
      }

      sourceJobBuffer.guid=jobs.guid;
      sourceJobBuffer.location=jobs.location;
      sourceJobBuffer.reqid=jobs.reqid;
      sourceJobBuffer.state=jobs.state;
      sourceJobBuffer.stateshort=jobs.state_short;
      sourceJobBuffer.title=jobs.title;
      sourceJobBuffer.uid=jobs.uid;
      sourceJobBuffer.url=jobs.url;
      }

      }
      catch (Exception e)
      {
      FailComponent(e.ToString());
      }

      }


      private source GetWebServiceResult(string wUrl)
      {

      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      source xmlResponse = null;

      try
      {
      //Test the connection
      if (httpWResp.StatusCode == HttpStatusCode.OK)
      {

      Stream responseStream = httpWResp.GetResponseStream();


      using (StreamReader reader = new StreamReader(responseStream))
      {
      //Deserialize our XML
      XmlSerializer sr = new XmlSerializer(typeof(source));
      xmlResponse = (source)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;

      }


      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

      Delete
    3. #region XML Classes


      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      [System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = true)]

      public partial class source
      {

      private string publisherField;

      private string publisherurlField;

      private string lastBuildDateField;

      private Link[] linkField;

      private Job[] jobField;



      public string publisher
      {
      get
      {
      return this.publisherField;
      }
      set
      {
      this.publisherField = value;
      }
      }



      public string publisherurl
      {
      get
      {
      return this.publisherurlField;
      }
      set
      {
      this.publisherurlField = value;
      }
      }



      public string lastBuildDate
      {
      get
      {
      return this.lastBuildDateField;
      }
      set
      {
      this.lastBuildDateField = value;
      }
      }


      [System.Xml.Serialization.XmlElementAttribute("link")]
      public Link[] link
      {
      get
      {
      return this.linkField;
      }
      set
      {
      this.linkField = value;
      }
      }


      [System.Xml.Serialization.XmlElementAttribute("job")]
      public Job[] job
      {
      get
      {
      return this.jobField;
      }
      set
      {
      this.jobField = value;
      }
      }
      }


      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class Link
      {

      private string hrefField;

      private string relField;


      [System.Xml.Serialization.XmlAttributeAttribute()]

      public string href
      {
      get
      {
      return this.hrefField;
      }
      set
      {
      this.hrefField = value;
      }
      }


      [System.Xml.Serialization.XmlAttributeAttribute()]

      public string rel
      {
      get
      {
      return this.relField;
      }
      set
      {
      this.relField = value;
      }
      }
      }

      Delete
    4. [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class Job
      {

      private string country_shortField;

      private string cityField;

      private string descriptionField;

      private string date_newField;

      private string countryField;

      private string companyField;

      private string titleField;

      private string stateField;

      private string reqidField;

      private string state_shortField;

      private string locationField;

      private string guidField;

      private string uidField;

      private string urlField;


      public string country_short
      {
      get
      {
      return this.country_shortField;
      }
      set
      {
      this.country_shortField = value;
      }
      }


      public string city
      {
      get
      {
      return this.cityField;
      }
      set
      {
      this.cityField = value;
      }
      }


      public string description
      {
      get
      {
      return this.descriptionField;
      }
      set
      {
      this.descriptionField = value;
      }
      }


      public string date_new
      {
      get
      {
      return this.date_newField;
      }
      set
      {
      this.date_newField = value;
      }
      }


      public string country
      {
      get
      {
      return this.countryField;
      }
      set
      {
      this.countryField = value;
      }
      }


      public string company
      {
      get
      {
      return this.companyField;
      }
      set
      {
      this.companyField = value;
      }
      }


      public string title
      {
      get
      {
      return this.titleField;
      }
      set
      {
      this.titleField = value;
      }
      }


      public string state
      {
      get
      {
      return this.stateField;
      }
      set
      {
      this.stateField = value;
      }
      }


      public string reqid
      {
      get
      {
      return this.reqidField;
      }
      set
      {
      this.reqidField = value;
      }
      }


      public string state_short
      {
      get
      {
      return this.state_shortField;
      }
      set
      {
      this.state_shortField = value;
      }
      }


      public string location
      {
      get
      {
      return this.locationField;
      }
      set
      {
      this.locationField = value;
      }
      }


      public string guid
      {
      get
      {
      return this.guidField;
      }
      set
      {
      this.guidField = value;
      }
      }


      public string uid
      {
      get
      {
      return this.uidField;
      }
      set
      {
      this.uidField = value;
      }
      }


      public string url
      {
      get
      {
      return this.urlField;
      }
      set
      {
      this.urlField = value;
      }
      }
      }


      #endregion

      Delete
    5. Sorry I had to break out the code into sections since there is a max allowable char count for comments in blogger:

      3. Send each output to data destinations that match the structure of the output. i.e. DT_STR 50 = varchar(50), DT_TEXT = varchar(max),etc.

      Delete
    6. I didn't add the ssis variables to the code, I think you can figure that part out. If not reply back.

      Delete
    7. Wow, you are awesome!! works great, will need some time to start grasping whats going on behind the curtains, thank you very much!

      Delete
  19. Hi, its a bit out of the scope of this component, but here it goes: I am trying to iterate through the results increasing the offset by 1000. So what I did is, I have created a forloop container (that is using two variable to loop from 1 to 5) and a script task that has the offset variable(as readwrite) and which should increase offset by 1000 each loop. The script builds, but I get an exception when I run the package:

    System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    I the script is empty it runs ok, so it must be something in this code

    public void Main()
    {

    Dts.Variables["User::offset"].Value = (int)Dts.Variables["User::offset"].Value + 1000;
    Dts.TaskResult = (int)ScriptResults.Success;
    }

    What do you think?

    ReplyDelete
  20. Got it to run after grouping the dataflow and the new script component. It would return 5 times the same thing so I changed the script to use the offset variable, but now it says:

    "The collection of variables locked for read and write access is not available outside of PostExecute."

    ReplyDelete
  21. I think I managed it, I foolishly set the offset variable as readwrite for the XMLSource Script component, changed it back to read-only and everything works perfectly, must say its a good feeling. Thanks for all the help and sorry for this monologue.

    ReplyDelete
    Replies
    1. Np man, sorry I didn't help, just saw the comments now. But it sounds like you worked through it yourself anyways. Gl with your project. Reply back if you run into another snag. Looping through record offsets can be tricky in some web api's and I don't know if this site offers documentation at all for it.

      Delete
  22. Hi Jim,
    Great post here but I am struggling for a day now...
    I am trying to read from a webservice that needs credentials (username, password and contract number), I guess this is the reason why I don't get anything back in the dataviewer.
    Where should I pass these credentials?

    Any help is appreciated :)

    ReplyDelete
    Replies
    1. What webservice is this? Do they have any API documentation?

      Delete
    2. Yeah I had to contact them and get some sample codes (only VB though). It was strange it didn't work in C# :) But I got it now...

      Thanks for your time anyway :)

      Delete
    3. Could you please let me know .. how do u provide the credentials. This is same with me

      Delete
  23. Hello! this is quite an useful code. Im new in this and I've been struggling trying to make it work in this site:
    http://api.sbif.cl/api-sbifv3/recursos_api/uf/periodo/2014/09/2014/10?apikey=208fba343b0a69bc08f291cc8d09eda62d4d3fa3&formato=xml

    Tthe catch is, the "2014/09/2014/10" part of the code must change every month and I just couldn't get it to work. I would much appreciate your help.

    ReplyDelete
    Replies
    1. Do you have some code developed already? Have you tried making some SSIS variables for the dates, handle changing the variables in the SSIS package and passing those variables to the script component?

      Delete
  24. This comment has been removed by the author.

    ReplyDelete
  25. I think I'm very close to implementing your method... my complexity appears similar to the person that you helped a year ago (upordownnow above). I have to post in 4 parts.
    try
    {
    //Call getWebServiceResult to return our DailyCall array
    Callmeasurement outPutStats = GetWebServiceResult(wUrl);

    //For each group of metrics output records
    foreach (var call in outPutStats.MCall)
    {

    Output0Buffer.AddRow();
    Output0Buffer.callid = call.callid;
    Output0Buffer.siteid = call.siteid;
    Output0Buffer.date = call.date;
    Output0Buffer.time = call.time;
    Output0Buffer.timetoanswer = call.timetoanswer;
    Output0Buffer.duration = call.duration;
    Output0Buffer.billedminutes = call.billedminutes;
    Output0Buffer.disposition = call.disposition;
    Output0Buffer.ringto = call.ringto;
    Output0Buffer.dnisnumber = call.dnisnumber;
    Output0Buffer.dnislabel1 = call.dnislabel1;
    Output0Buffer.dnislabel2 = call.dnislabel2;
    Output0Buffer.dnislabel1 = call.dnislabel3;
    Output0Buffer.dnislabel2 = call.dnislabel4;
    Output0Buffer.dnislabel1 = call.dnislabel5;
    Output0Buffer.dnislabel2 = call.dnislabel6;
    Output0Buffer.dnislabel1 = call.dnislabel7;
    Output0Buffer.dnislabel2 = call.dnislabel8;
    Output0Buffer.dnislabel1 = call.dnislabel9;
    Output0Buffer.dnislabel2 = call.dnislabel10;
    Output0Buffer.dnislabel1 = call.dnislabel11;
    Output0Buffer.dnislabel2 = call.dnislabel12;
    Output0Buffer.dnislabel1 = call.dnislabel13;
    Output0Buffer.dnislabel2 = call.dnislabel14;
    Output0Buffer.dnislabel1 = call.dnislabel15;
    Output0Buffer.custnumber = call.custnumber;
    Output0Buffer.custcity = call.custcity;
    Output0Buffer.custstate = call.custstate;
    Output0Buffer.custpostalcode = call.custpostalcode;
    Output0Buffer.phonecodeid = call.phonecodeid;
    Output0Buffer.phonecodename = call.phonecodename;
    Output0Buffer.audio = call.audio;
    }

    }
    catch (Exception e)
    {
    FailComponent(e.ToString());
    }

    }

    ReplyDelete
  26. private Callmeasurement GetWebServiceResult(string wUrl)
    {

    HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
    HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
    Callmeasurement 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(Callmeasurement));
    xmlResponse = (Callmeasurement)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 an 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);

    }
    #endregion

    }
    #endregion

    ReplyDelete
  27. ///
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    [System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = false)]
    public partial class callmeasurement
    {

    private callmeasurementCall callField;

    ///
    public callmeasurementCall call
    {
    get
    {
    return this.callField;
    }
    set
    {
    this.callField = value;
    }
    }
    }

    ///
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCall
    {

    private ushort site_idField;

    private System.DateTime dateField;

    private string timeField;

    private byte time_to_answerField;

    private System.DateTime durationField;

    private decimal billed_minutesField;

    private string dispositionField;

    private uint ring_toField;

    private callmeasurementCallDnis dnisField;

    private callmeasurementCallCustomer customerField;

    private callmeasurementCallPhonecode phonecodeField;

    private string audioField;

    private ulong idField;

    ///
    public ushort site_id
    {
    get
    {
    return this.site_idField;
    }
    set
    {
    this.site_idField = value;
    }
    }

    ///
    [System.Xml.Serialization.XmlElementAttribute(DataType = "date")]
    public System.DateTime date
    {
    get
    {
    return this.dateField;
    }
    set
    {
    this.dateField = value;
    }
    }

    ///
    public string time
    {
    get
    {
    return this.timeField;
    }
    set
    {
    this.timeField = value;
    }
    }

    ///
    public byte time_to_answer
    {
    get
    {
    return this.time_to_answerField;
    }
    set
    {
    this.time_to_answerField = value;
    }
    }

    ///
    [System.Xml.Serialization.XmlElementAttribute(DataType = "time")]
    public System.DateTime duration
    {
    get
    {
    return this.durationField;
    }
    set
    {
    this.durationField = value;
    }
    }

    ///
    public decimal billed_minutes
    {
    get
    {
    return this.billed_minutesField;
    }
    set
    {
    this.billed_minutesField = value;
    }
    }

    ///
    public string disposition
    {
    get
    {
    return this.dispositionField;
    }
    set
    {
    this.dispositionField = value;
    }
    }

    ///
    public uint ring_to
    {
    get
    {
    return this.ring_toField;
    }
    set
    {
    this.ring_toField = value;
    }
    }

    ///
    public callmeasurementCallDnis dnis
    {
    get
    {
    return this.dnisField;
    }
    set
    {
    this.dnisField = value;
    }
    }

    ///
    public callmeasurementCallCustomer customer
    {
    get
    {
    return this.customerField;
    }
    set
    {
    this.customerField = value;
    }
    }

    ///
    public callmeasurementCallPhonecode phonecode
    {
    get
    {
    return this.phonecodeField;
    }
    set
    {
    this.phonecodeField = value;
    }
    }

    ///
    public string audio
    {
    get
    {
    return this.audioField;
    }
    set
    {
    this.audioField = value;
    }
    }

    ReplyDelete
  28. ///
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public ulong id
    {
    get
    {
    return this.idField;
    }
    set
    {
    this.idField = value;
    }
    }
    }

    ///
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallDnis
    {

    private string numberField;

    private callmeasurementCallDnisLabel[] labelField;

    ///
    public string number
    {
    get
    {
    return this.numberField;
    }
    set
    {
    this.numberField = value;
    }
    }

    ///
    [System.Xml.Serialization.XmlElementAttribute("label")]
    public callmeasurementCallDnisLabel[] label
    {
    get
    {
    return this.labelField;
    }
    set
    {
    this.labelField = value;
    }
    }
    }

    ///
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallDnisLabel
    {

    private byte placeField;

    private string valueField;

    ///
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public byte place
    {
    get
    {
    return this.placeField;
    }
    set
    {
    this.placeField = value;
    }
    }

    ///
    [System.Xml.Serialization.XmlTextAttribute()]
    public string Value
    {
    get
    {
    return this.valueField;
    }
    set
    {
    this.valueField = value;
    }
    }
    }

    ///
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallCustomer
    {

    private ulong numberField;

    private string cityField;

    private string stateField;

    private ushort postalcodeField;

    ///
    public ulong number
    {
    get
    {
    return this.numberField;
    }
    set
    {
    this.numberField = value;
    }
    }

    ///
    public string city
    {
    get
    {
    return this.cityField;
    }
    set
    {
    this.cityField = value;
    }
    }

    ///
    public string state
    {
    get
    {
    return this.stateField;
    }
    set
    {
    this.stateField = value;
    }
    }

    ///
    public ushort postalcode
    {
    get
    {
    return this.postalcodeField;
    }
    set
    {
    this.postalcodeField = value;
    }
    }
    }

    ///
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallPhonecode
    {

    private string idField;

    private string nameField;

    ///
    public string id
    {
    get
    {
    return this.idField;
    }
    set
    {
    this.idField = value;
    }
    }

    ///
    public string name
    {
    get
    {
    return this.nameField;
    }
    set
    {
    this.nameField = value;
    }
    }
    }

    ReplyDelete
  29. FYI- I didn't post the beginning of the script because of the length. I can run a previous version successfully, but most my columns contain null values.

    ReplyDelete
  30. I realized I needed the entire doc when Paste as XML Classes. Following class changed...
    public partial class callmeasurement
    {

    private callmeasurementCall[] callField;

    ///
    [System.Xml.Serialization.XmlElementAttribute("call")]
    public callmeasurementCall[] call
    {
    get
    {
    return this.callField;
    }
    set
    {
    this.callField = value;
    }
    }
    }

    ReplyDelete
    Replies
    1. Do you still need help? Can you share some sample XML so I can help troubleshoot?

      Delete
    2. Help would be great. The following is a sample of a single call from a daily feed for which there are 1000+ calls. I substitute &lt/&gt for tags in order to post.

      &lt?xml version="1.0" encoding="UTF-8"?%gt
      &ltcallmeasurement%gt
      &ltcall id="11111111111"%gt
      &ltsite_id%gt99999&lt/site_id%gt
      &ltdate%gt2015-06-01&lt/date%gt
      &lttime%gt07:21:47 AM&lt/time%gt
      &lttime_to_answer%gt4&lt/time_to_answer%gt
      &ltduration%gt00:00:10&lt/duration%gt
      &ltbilled_minutes%gt0.33&lt/billed_minutes%gt
      &ltdisposition%gtAnswered&lt/disposition%gt
      &ltring_to%gt7777777777&lt/ring_to%gt
      &ltdnis%gt
      &ltnumber%gt123-123-1234&lt/number%gt
      &ltlabel place="1"%gt99999-FirstName LastName&lt/label%gt
      &ltlabel place="2"%gt111111 Product(Y)&lt/label%gt
      &ltlabel place="3"%gtDTL&lt/label%gt
      &ltlabel place="4"%gt$891.00&lt/label%gt
      &ltlabel place="5"%gt6/1/2014&lt/label%gt
      &ltlabel place="6"%gt3/7/2014&lt/label%gt
      &ltlabel place="7"%gtPublisher&lt/label%gt
      &ltlabel place="8"%gtProductBrand&lt/label%gt
      &ltlabel place="9"%gtCorporate&lt/label%gt
      &ltlabel place="10"%gt06/01/2014&lt/label%gt
      &ltlabel place="12"%gt$890.80&lt/label%gt
      &ltlabel place="13"%gtCorporate&lt/label%gt
      &ltlabel place="14"%gt2013-06-01&lt/label%gt
      &ltlabel place="15"%gtRef end 8-31-13 Ported-10-30-2012&lt/label%gt
      &lt/dnis%gt
      &ltcustomer%gt
      &ltnumber%gt8888888888&lt/number%gt
      &ltcity%gtCAMDEN-CENTRAL&lt/city%gt
      &ltstate%gtNJ&lt/state%gt
      &ltpostalcode%gt12345&lt/postalcode%gt
      &lt/customer%gt
      &ltphonecode%gt
      &ltid%gtunknown&lt/id%gt
      &ltname%gtunknown&lt/name%gt
      &lt/phonecode%gt
      &ltaudio%gthttp://www.url.com/review_x.cfm?cid=11111111111&lid=99999&lt/audio%gt
      &lt/call%gt
      &lt/callmeasurement%gt

      Delete
    3. Here's how I got this to work.

      I created 2 outputs on the inputs and outputs screen:

      callmeasurement

      call_id DT_I8
      site_id DT_I4
      date DT_DBTIMESTAMP
      time DT_STR 11
      time_to_answer DT_I4
      duration DT_STR 8
      billed_minutes DT_DECIMAL
      disposition DT_STR 50
      ring_to DT_I8
      dnis_number DT_STR 12
      customer_number DT_I8
      customer_city DT_STR 50
      customer_postalcode DT_I4
      phonecode_id DT_STR 50
      phonecode_name DT_STR 50
      audio DT_STR 100

      label

      call_id DT_I8
      dnis_number DT_STR 12
      label_place DT_I4
      label_value DT_STR 50

      You can join callmeasurement to label on call_id and dnis_number. The code is:

      Delete
    4. #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()
      {

      //Set Webservice URL
      string wUrl = "YOUR URL GOES HERE";


      try
      {
      //Call getWebServiceResult to return our callmeasurement object
      callmeasurement outPutCallMeasurement = GetWebServiceResult(wUrl);

      if(outPutCallMeasurement != null)
      {
      callmeasurementBuffer.AddRow();
      callmeasurementBuffer.callid=outPutCallMeasurement.call.id;
      callmeasurementBuffer.siteid=outPutCallMeasurement.call.site_id;
      callmeasurementBuffer.date=outPutCallMeasurement.call.date;
      callmeasurementBuffer.time=outPutCallMeasurement.call.time;
      callmeasurementBuffer.timetoanswer=outPutCallMeasurement.call.time_to_answer;
      callmeasurementBuffer.duration=outPutCallMeasurement.call.duration;
      callmeasurementBuffer.billedminutes=outPutCallMeasurement.call.billed_minutes;
      callmeasurementBuffer.disposition=outPutCallMeasurement.call.disposition;
      callmeasurementBuffer.ringto=outPutCallMeasurement.call.ring_to;
      callmeasurementBuffer.dnisnumber=outPutCallMeasurement.call.dnis.number;
      callmeasurementBuffer.customernumber=outPutCallMeasurement.call.customer.number;
      callmeasurementBuffer.customercity=outPutCallMeasurement.call.customer.city;
      callmeasurementBuffer.customerpostalcode=outPutCallMeasurement.call.customer.postalcode;
      callmeasurementBuffer.phonecodeid=outPutCallMeasurement.call.phonecode.id;
      callmeasurementBuffer.phonecodename=outPutCallMeasurement.call.phonecode.name;
      callmeasurementBuffer.audio=outPutCallMeasurement.call.audio;

      foreach(callmeasurementCallDnisLabel label in outPutCallMeasurement.call.dnis.label)
      {
      labelBuffer.AddRow();
      labelBuffer.callid=outPutCallMeasurement.call.id;
      labelBuffer.dnisnumber=outPutCallMeasurement.call.dnis.number;
      labelBuffer.labelplace=label.place;
      labelBuffer.labelvalue=label.value;
      }


      }

      }
      catch (Exception e)
      {
      FailComponent(e.ToString());
      }

      }

      Delete
    5. /// < summary >
      /// Method to call the web service and return our callmeasurement object
      /// < /summary >
      /// < param name="wUrl" >The web service URL< /param >
      /// < returns >An array of call measurement object which contains the deserialized XML< /returns >
      public callmeasurement GetWebServiceResult(string wUrl)
      {

      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      callmeasurement 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))
      {

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

      using (var sReader = new StringReader(reader.ReadToEnd().Replace("&","&")))
      {
      xmlResponse = (callmeasurement)sr.Deserialize(sReader);
      }

      }

      }
      //Output connection error message
      else
      {
      FailComponent(httpWResp.StatusCode.ToString());

      }
      }
      //Output xml parsing error
      catch (Exception e)
      {
      FailComponent(e.ToString());
      }
      return xmlResponse;

      }

      Delete
    6. /// < summary >
      /// Outputs an error message
      /// < /summary >
      /// < param name="errorMsg" >The error message< /param >
      public 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


      //< callmeasurement >
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      [System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = false)]
      public partial class callmeasurement
      {
      public callmeasurementCall call;
      }

      //< call >
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class callmeasurementCall
      {
      //< site_id >
      public int site_id;
      //< date >
      [System.Xml.Serialization.XmlElementAttribute(DataType = "date")]
      public System.DateTime date;
      //< time >
      public string time;
      //< time_to_answer >
      public byte time_to_answer;
      //< duration >
      public string duration;
      //< billed_minutes >
      public decimal billed_minutes;
      //< disposition >
      public string disposition;
      //< ring_to >
      public long ring_to;
      //< dnis >
      public callmeasurementCallDnis dnis;
      //< customer >
      public callmeasurementCallCustomer customer;
      //
      public callmeasurementCallPhonecode phonecode;
      //< audio >
      public string audio;
      //< call id="" >
      [System.Xml.Serialization.XmlAttributeAttribute()]
      public long id;

      }

      //
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class callmeasurementCallDnis
      {
      //< number >
      public string number;

      //< label >
      [System.Xml.Serialization.XmlElementAttribute("label")]
      public callmeasurementCallDnisLabel[] label;

      }

      //< label >
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class callmeasurementCallDnisLabel
      { //< label place=" ">
      [System.Xml.Serialization.XmlAttributeAttribute()]
      public int place;
      //< label >
      [System.Xml.Serialization.XmlTextAttribute()]
      public string value;


      }

      //< customer >
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class callmeasurementCallCustomer
      {
      //< number >
      public long number;
      //< city >
      public string city;
      //< state >
      public string state;
      //< postalcode >
      public ushort postalcode;

      }

      //< phonecode >
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class callmeasurementCallPhonecode
      {
      //< id >
      public string id;
      //< name >
      public string name;


      }
      #endregion

      Delete
    7. Hmm the post is replacing values....using (var sReader = new StringReader(reader.ReadToEnd().Replace("&","&")))

      is replacing the second value in the Replace method. It should be: & amp ; (remove the spaces)

      Delete
    8. XML is :

      < rootnode >
      < T1 >
      < T2 >
      < EMPNAME >PARUL< /EMPNAME >
      < EMPID >2015< /EMPID >
      < T3 >
      < T3NAME >NAME1< /T3NAME >
      < T3VALUE >VALUE1< /T3VALUE >
      < /T3 >
      < T3 >
      < T3NAME >NAME2< /T3NAME >
      < T3VALUE >VALUE2< /T3VALUE >
      < /T3 >
      < /T2 >
      < T1 >
      < T2 >
      < EMPNAME >PARUL2< /EMPNAME >
      < EMPID >20152< /EMPID >
      < T3 >
      < T3NAME >NAME12< /T3NAME >
      < T3VALUE >VALUE12< /T3VALUE >
      < /T3 >
      < T3 >
      < T3NAME >NAME22< /T3NAME >
      < T3VALUE >VALUE22< /T3VALUE >
      < /T3 >
      < /T2 >
      < /T1 >
      < /ROOTNODE >

      Delete
    9. 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()
      {

      //Set Webservice URL
      string wUrl = “web api address”
      try
      {

      //Call getWebServiceResult to return our DailyStat array
      DataSet outPutStats = getWebServiceResult(wUrl);


      //For each output
      foreach (var stats in outPutStats.ChannelArray)
      {


      //For each group of metrics output records

      foreach (var records in stats.ItemArray)
      {

      Output0Buffer.AddRow();
      Output0Buffer.empname = records.empname;
      Output0Buffer.empid = records.empid;

      ;


      if (records.cust.t3name == "cust name")
      {

      Output0Buffer.custname = records.cust.t3value;

      }


      }
      }

      }



      catch (Exception e)
      {
      failComponent(e.ToString());
      }


      }

      //Method to return our DailyStat array
      private DataSet getWebServiceResult(string wUrl)
      {

      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      DataSet 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(DataSet));
      xmlResponse = (DataSet)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 our array of
      [XmlRootAttribute("rootnode")]
      public class DataSet
      {
      [XmlElement("t1")]
      public t1[] t1Array { get; set;}
      }

      public class t1
      {
      [XmlElement("t2")]
      public t2[] t2Array { get; set; }
      }

      public class t3
      {
      [XmlElement("t3")]
      public string t3name { get; set; }
      public string t3value { get; set; }
      }

      public class t2
      {

      public string empname { get; set; }
      public string empid { get; set; }

      public t3 cust { get; set; }

      }

      Delete
  31. Hi

    Can you help me out on this :

    XML is :




    PARUL
    2015

    NAME1
    VALUE1


    NAME2
    VALUE2




    PARUL2
    20152

    NAME12
    VALUE12


    NAME22
    VALUE22



    ReplyDelete
  32. "Hi

    Can you help me out on this :

    XML is :




    PARUL
    2015

    NAME1
    VALUE1


    NAME2
    VALUE2




    PARUL2
    20152

    NAME12
    VALUE12


    NAME22
    VALUE22




    "

    ReplyDelete
  33. "Hi

    Can you help me out on this :

    XML is :


    then
    inside it
    thenPARUL
    then 2015
    then
    then NAME1
    then VALUE1
    then

    NAME2
    VALUE2




    PARUL2
    20152

    NAME12
    VALUE12


    NAME22
    VALUE22




    "

    ReplyDelete
  34. i am not able to paste my xml here

    ReplyDelete
    Replies
    1. Put a space between the chevrons and the data i.e. < NAME2 > . Also what have you tried so far?

      Delete
    2. XML is :

      < rootnode >
      < T1 >
      < T2 >
      < EMPNAME >PARUL< /EMPNAME >
      < EMPID >2015< /EMPID >
      < T3 >
      < T3NAME >NAME1< /T3NAME >
      < T3VALUE >VALUE1< /T3VALUE >
      < /T3 >
      < T3 >
      < T3NAME >NAME2< /T3NAME >
      < T3VALUE >VALUE2< /T3VALUE >
      < /T3 >
      < /T2 >
      < T1 >
      < T2 >
      < EMPNAME >PARUL2< /EMPNAME >
      < EMPID >20152< /EMPID >
      < T3 >
      < T3NAME >NAME12< /T3NAME >
      < T3VALUE >VALUE12< /T3VALUE >
      < /T3 >
      < T3 >
      < T3NAME >NAME22< /T3NAME >
      < T3VALUE >VALUE22< /T3VALUE >
      < /T3 >
      < /T2 >
      < /T1 >
      < /ROOTNODE >

      Delete
    3. Can you help me out on this . i am new to xml

      Delete
    4. 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()
      {

      //Set Webservice URL
      string wUrl = “web api address”
      try
      {

      //Call getWebServiceResult to return our DailyStat array
      DataSet outPutStats = getWebServiceResult(wUrl);


      //For each output
      foreach (var stats in outPutStats.ChannelArray)
      {


      //For each group of metrics output records

      foreach (var records in stats.ItemArray)
      {

      Output0Buffer.AddRow();
      Output0Buffer.empname = records.empname;
      Output0Buffer.empid = records.empid;

      ;


      if (records.cust.t3name == "cust name")
      {

      Output0Buffer.custname = records.cust.t3value;

      }


      }
      }

      }



      catch (Exception e)
      {
      failComponent(e.ToString());
      }


      }

      //Method to return our DailyStat array
      private DataSet getWebServiceResult(string wUrl)
      {

      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      DataSet 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(DataSet));
      xmlResponse = (DataSet)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 our array of
      [XmlRootAttribute("rootnode")]
      public class DataSet
      {
      [XmlElement("t1")]
      public t1[] t1Array { get; set;}
      }

      public class t1
      {
      [XmlElement("t2")]
      public t2[] t2Array { get; set; }
      }

      public class t3
      {
      [XmlElement("t3")]
      public string t3name { get; set; }
      public string t3value { get; set; }
      }

      public class t2
      {

      public string empname { get; set; }
      public string empid { get; set; }

      public t3 cust { get; set; }

      }

      Delete
    5. This comment has been removed by the author.

      Delete
    6. Hi Jim,

      I have posted the code and XML . Can you look into it and help me out

      Delete
    7. This comment has been removed by the author.

      Delete
    8. i am getting null reference error for t3

      Delete
    9. i am waiting for your reply....
      Can you guide if the classes created re correct or not??

      Delete
    10. Woah there buddy...I do have a day job you know...

      Here's how I got this to work. I created 2 outputs on the inputs and outputs screen:

      Output 0
      empname DT_STR 50
      empid DT_I4

      Customer
      custname DT_STR 50

      C# code is as follows:

      Delete
    11. #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.
      ///
      [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
      public class ScriptMain : UserComponent
      {

      #region Methods
      /// < summary >Outputs records to the output buffer
      public override void CreateNewOutputRows()
      {


      //Set Webservice URL
      string wUrl = "http://yourwebserviceurl/Respiratory/ResService.asmx/getAgentMetricsXml?offerDate=";

      try
      {
      //Call getWebServiceResult to return our RootObject object
      ROOTNODE outPutRootObject = GetWebServiceResult(wUrl);

      if (outPutRootObject != null)
      {
      foreach (ROOTNODET1 T1 in outPutRootObject.T1)
      {

      Output0Buffer.AddRow();
      Output0Buffer.empid = T1.T2.EMPID;
      Output0Buffer.empname = T1.T2.EMPNAME;

      foreach (ROOTNODET1T2T3 T3 in T1.T2.T3)
      {
      if (T3.T3NAME == "cust name")
      {
      CustomerBuffer.AddRow();
      CustomerBuffer.custname = T3.T3NAME;
      }
      }

      }




      }

      }
      catch (Exception e)
      {
      FailComponent(e.ToString());
      }

      }

      Delete
    12. /// < summary >
      /// Method to call the web service and return our ROOTNODE
      ///
      /// < param name="wUrl" >The web service URL< /param >
      /// < returns >The ROOTNODE which contains the deserialized XML< /returns >
      private ROOTNODE GetWebServiceResult(string wUrl)
      {

      HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);
      HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();
      ROOTNODE 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(ROOTNODE));
      xmlResponse = (ROOTNODE)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

      Delete
    13. #region XML Classes


      /// < remarks />
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      [System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = false)]
      public partial class ROOTNODE
      {

      private ROOTNODET1[] t1Field;

      /// < remarks />
      [System.Xml.Serialization.XmlElementAttribute("T1")]
      public ROOTNODET1[] T1
      {
      get
      {
      return this.t1Field;
      }
      set
      {
      this.t1Field = value;
      }
      }
      }

      /// < remarks />
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class ROOTNODET1
      {

      private ROOTNODET1T2 t2Field;

      /// < remarks />
      public ROOTNODET1T2 T2
      {
      get
      {
      return this.t2Field;
      }
      set
      {
      this.t2Field = value;
      }
      }
      }

      /// < remarks />
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class ROOTNODET1T2
      {

      private string eMPNAMEField;

      private int eMPIDField;

      private ROOTNODET1T2T3[] t3Field;

      /// < remarks />
      public string EMPNAME
      {
      get
      {
      return this.eMPNAMEField;
      }
      set
      {
      this.eMPNAMEField = value;
      }
      }

      /// < remarks />
      public int EMPID
      {
      get
      {
      return this.eMPIDField;
      }
      set
      {
      this.eMPIDField = value;
      }
      }

      /// < remarks />
      [System.Xml.Serialization.XmlElementAttribute("T3")]
      public ROOTNODET1T2T3[] T3
      {
      get
      {
      return this.t3Field;
      }
      set
      {
      this.t3Field = value;
      }
      }
      }

      /// < remarks />
      [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
      public partial class ROOTNODET1T2T3
      {

      private string t3NAMEField;

      private string t3VALUEField;

      /// < remarks />
      public string T3NAME
      {
      get
      {
      return this.t3NAMEField;
      }
      set
      {
      this.t3NAMEField = value;
      }
      }

      /// < remarks />
      public string T3VALUE
      {
      get
      {
      return this.t3VALUEField;
      }
      set
      {
      this.t3VALUEField = value;
      }
      }
      }


      #endregion

      Delete
    14. And Just an FYI...I think the XML you posted is incorrect. I think you meant this:

      < ROOTNODE >
      < T1 >
      < T2 >
      < EMPNAME > PARUL< /EMPNAME >
      < EMPID > 2015< /EMPID >
      < T3 >
      < T3NAME > NAME1< /T3NAME >
      < T3VALUE > VALUE1< /T3VALUE >
      < /T3 >
      < T3 >
      < T3NAME > NAME2< /T3NAME >
      < T3VALUE > VALUE2< /T3VALUE >
      < /T3 >
      < /T2 >
      < /T1 >
      < T1 >
      < T2 >
      < EMPNAME > PARUL2< /EMPNAME >
      < EMPID > 20152< /EMPID >
      < T3 >
      < T3NAME > NAME12< /T3NAME >
      < T3VALUE > VALUE12< /T3VALUE >
      < /T3 >
      < T3 >
      < T3NAME > NAME22< /T3NAME >
      < T3VALUE > VALUE22< /T3VALUE >
      < /T3 >
      < /T2 >
      < /T1 >
      < /ROOTNODE >

      Delete
    15. Hi Jim,

      Thanks for your help.
      But the xml that i posted was correct.

      Delete
    16. Hi Jim.
      Thanks for the support. I modified my earlier code.
      Ur site and codes are really awesome.

      Delete
  35. Hi Jim-
    Trying to make the Call an array. Here are my changes to the XML classes.
    #region XML Classes


    //< callmeasurement >
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    [System.Xml.Serialization.XmlRootAttribute(Namespace = "", IsNullable = false)]
    // Begin orig
    // public partial class callmeasurement
    //{
    // public callmeasurementCall call;
    //}
    // end orig
    //begin new
    public partial class callmeasurement
    {

    // private callmeasurementCall[] callField;

    ///
    [System.Xml.Serialization.XmlElementAttribute("call")]
    public callmeasurementCall[] call;
    //{
    // get
    // {
    // return this.callField;
    // }
    // set
    // {
    // this.callField = value;
    // }
    //}
    }

    //end new
    //< call >
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCall
    {
    //< site_id >
    public int site_id;
    //< date >
    [System.Xml.Serialization.XmlElementAttribute(DataType = "date")]
    public System.DateTime date;
    //< time >
    public string time;
    //< time_to_answer >
    public string time_to_answer;
    //< duration >
    public string duration;
    //< billed_minutes >
    public string billed_minutes;
    //< disposition >
    public string disposition;
    //< ring_to >
    public string ring_to;
    //< dnis >
    public callmeasurementCallDnis dnis;
    //< customer >
    public callmeasurementCallCustomer customer;
    //
    public callmeasurementCallPhonecode phonecode;
    //< audio >
    public string audio;
    //< call id="" >
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public long id;

    }

    //
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallDnis
    {
    //< dnis_number >
    public string number;

    //< label >
    [System.Xml.Serialization.XmlElementAttribute("label")]
    public callmeasurementCallDnisLabel[] label;

    }

    //< label >
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallDnisLabel
    { //< label place="">
    [System.Xml.Serialization.XmlAttributeAttribute()]
    public int place;
    //< label >
    [System.Xml.Serialization.XmlTextAttribute()]
    public string value;


    }

    //< customer >
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallCustomer
    {
    //< number >
    public string number;
    //< city >
    public string city;
    //< state >
    public string state;
    //< postalcode >
    public string postalcode;

    }

    //< phonecode >
    [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
    public partial class callmeasurementCallPhonecode
    {
    //< id >
    public string id;
    //< name >
    public string name;


    }
    #endregion

    ReplyDelete
    Replies
    1. Here is where I have added a foreach call change to the method..
      try
      {
      //Call getWebServiceResult to return our callmeasurement object
      callmeasurement outPutCallMeasurement = GetWebServiceResult(wUrl);

      foreach (var call in outPutCallMeasurement.call)
      {

      if (outPutCallMeasurement != null)
      {
      callmeasurementBuffer.AddRow();
      callmeasurementBuffer.callid = outPutCallMeasurement.call.id;
      callmeasurementBuffer.siteid = outPutCallMeasurement.call.site_id;
      callmeasurementBuffer.calldate = outPutCallMeasurement.call.date;
      callmeasurementBuffer.calltime = outPutCallMeasurement.call.time;
      callmeasurementBuffer.timetoanswer = outPutCallMeasurement.call.time_to_answer;
      callmeasurementBuffer.duration = outPutCallMeasurement.call.duration;
      callmeasurementBuffer.billedminutes = outPutCallMeasurement.call.billed_minutes;
      callmeasurementBuffer.disposition = outPutCallMeasurement.call.disposition;
      callmeasurementBuffer.ringto = outPutCallMeasurement.call.ring_to;
      callmeasurementBuffer.dnisnumber = outPutCallMeasurement.call.dnis.number;
      callmeasurementBuffer.customerphone = outPutCallMeasurement.call.customer.number;
      callmeasurementBuffer.customercity = outPutCallMeasurement.call.customer.city;
      callmeasurementBuffer.customerpostalcode = outPutCallMeasurement.call.customer.postalcode;
      callmeasurementBuffer.phonecodeid = outPutCallMeasurement.call.phonecode.id;
      callmeasurementBuffer.phonecodename = outPutCallMeasurement.call.phonecode.name;
      callmeasurementBuffer.audio = outPutCallMeasurement.call.audio;

      foreach (callmeasurementCallDnisLabel label in outPutCallMeasurement.call.dnis.label)
      {
      labelBuffer.AddRow();
      labelBuffer.callid = outPutCallMeasurement.call.id;
      labelBuffer.dnisnumber = outPutCallMeasurement.call.dnis.number;
      labelBuffer.labelplace = label.place;
      labelBuffer.labelvalue = label.value;
      }
      }
      }

      }
      catch (Exception e)
      {
      FailComponent(e.ToString());
      }

      After adding the new foreach, I'm getting the following error for each element that I'm trying to deserialize... 'System.Array' does not contain a definition for 'id' and no extension method 'id' accepting a first argument of type 'System.Array' could be found (are you missing a using directive or an assembly reference?)

      Delete
    2. FYI the change to the XML classes is at the start and marked by "begin/end orig" and "begin/end new"

      Delete
    3. Try This:

      try
      {
      //Call getWebServiceResult to return our callmeasurement object
      callmeasurement outPutCallMeasurement = GetWebServiceResult(wUrl);

      foreach (callmeasurementCall call in outPutCallMeasurement.call)
      {

      if (outPutCallMeasurement != null)
      {
      callmeasurementBuffer.AddRow();
      callmeasurementBuffer.callid = call.id;
      callmeasurementBuffer.siteid = call.site_id;
      callmeasurementBuffer.calldate = call.date;
      callmeasurementBuffer.calltime = call.time;
      callmeasurementBuffer.timetoanswer = call.time_to_answer;
      callmeasurementBuffer.duration = call.duration;
      callmeasurementBuffer.billedminutes = call.billed_minutes;
      callmeasurementBuffer.disposition = call.disposition;
      callmeasurementBuffer.ringto = call.ring_to;
      callmeasurementBuffer.dnisnumber = call.dnis.number;
      callmeasurementBuffer.customerphone = call.customer.number;
      callmeasurementBuffer.customercity = call.customer.city;
      callmeasurementBuffer.customerpostalcode = call.customer.postalcode;
      callmeasurementBuffer.phonecodeid = call.phonecode.id;
      callmeasurementBuffer.phonecodename = call.phonecode.name;
      callmeasurementBuffer.audio = call.audio;

      foreach (callmeasurementCallDnisLabel label in call.dnis.label)
      {
      labelBuffer.AddRow();
      labelBuffer.callid = call.id;
      labelBuffer.dnisnumber = call.dnis.number;
      labelBuffer.labelplace = label.place;
      labelBuffer.labelvalue = label.value;
      }
      }
      }

      }
      catch (Exception e)
      {
      FailComponent(e.ToString());
      }

      Delete
  36. Hi Jim,

    Can you please suggest me some books from where i can learn advanced c# particularly related to consuming web services like the code u posted because i don't find such stuff in books.

    Appreciate your help.

    ReplyDelete