asked on

SSIS: How to shred XML variable and save parts as other variables

Hi All

I have a SSIS 2008 R2 package with a Script Task in C# {code supplied by someone else, butchered by me} that performs a Web Service call and stores the result in XML with three values in a string variable that looks like the below XML. Good to go.

<ArrayOf CustomerAccount xmlns:i="http://w3.org/blah blah blah" xmlns="http://blah blah blah">
<Customer Account>
   <ContactEmail> whacka@doo.com</ContactEmail> 
   <Name>Michael Stipe</Name>
   <Number>42</Number>
</CustomerAccount>
</ArrayOf CustomerAccount>

Open in new window

Question: Is there a way in script (or in anything else) to take this XML string variable, grab the three values, and store them as other variables?

Due to customer requirements I am not allowed to write this to a file or save these directly to a table without encrypt them first (I can handle the encryption part), as all three of these values are considered confidential.

Thanks in advance.
Jim

SOLUTION

Christopher Kile

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

ASKER

... looking ...

Jim Horn

ASKER

Both links described here (VB.NET) and here (C#) starts with reading from an .xml file, and I have my xml already in a string variable strResult.

Below current SSIS C# code, slightly mocked up, which I probably should have posted with the original question.

I have confirmed that the call works correctly and strResult contains the return XML that matches the one in the original question.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;

using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

using System.Net;
using System.IO;


namespace ST_32c35746b01c475385d5c827c55e0818.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
 
        public void Main()
        {

            string sCode1 = Dts.Variables["Code1"].Value.ToString();
            string sCode2 = Dts.Variables["Code2"].Value.ToString();

            string webservice = "";

            //hard coded way to verify it works.
            var url = "http://goo.foo.boo/payment/Method?Code1=" + sCode1 + "&Code2=" + sCode2;

            MessageBox.Show(url);

            var _httpWReq = (HttpWebRequest)WebRequest.Create(url);
            _httpWReq.Credentials = CredentialCache.DefaultNetworkCredentials;
            _httpWReq.Method = "GET";
            _httpWReq.ContentType = "text/xml";


            var x = (HttpWebResponse)_httpWReq.GetResponse(); //the exception :(
            var strResult = new StreamReader(x.GetResponseStream()).ReadToEnd();

            MessageBox.Show(strResult);

            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Open in new window

ASKER CERTIFIED SOLUTION

Christopher Kile

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Horn

ASKER

... looking ...

Yeah one of the requirements is that this confidential info can only be access via a web service, executed once for each record needed, and stored in tables only encrypted. I'd have much preferred a single T-SQL call grabbing the entire set, but it's their nickel.

Jim Horn

ASKER

Bingo bango.

The Web guys are still working on getting the method right, but based on the agreed-upon schema, hard-coded in the below script, we're good.

Thanks.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

using System.Net;
using System.IO;
using System.Xml;

namespace ST_32c35746b01c475385d5c827c55e0818.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            string sCustomerCode = Dts.Variables["sCustomerCode"].Value.ToString();
            string sSourceSystemCode = Dts.Variables["sSourceSystemCode"].Value.ToString();

            string webservice = "";

            //need to build through variables

            //hard coded way to verify it works.
            var url = "http://goo.foo.boo/payment/method?SourceSystemCode=027&CustomerCode=101120";

            MessageBox.Show(url);

            var _httpWReq = (HttpWebRequest)WebRequest.Create(url);
            _httpWReq.Credentials = CredentialCache.DefaultNetworkCredentials;
            _httpWReq.Method = "GET";
            _httpWReq.ContentType = "text/xml";


            var x = (HttpWebResponse)_httpWReq.GetResponse(); //the exception :(
            var strResult = new StreamReader(x.GetResponseStream()).ReadToEnd();

            MessageBox.Show(strResult);

            strResult = "<ArrayOfCustomerAccount>"
                            + "<CustomerAccount>"
                            + "<ContactEmail>whacka@doo.com</ContactEmail> "
                            + "<Name>Michael Stipe</Name>"
                            + "<Number>42</Number>"
                            + "</CustomerAccount>"
                            + "</ArrayOfCustomerAccount> ";

            // XmlTextReader resultXMLReader = new XmlTextReader(x.GetResponseStream());


            XmlTextReader resultXMLReader = new XmlTextReader(new StringReader(strResult));
            DataSet dsResult = new DataSet();

            dsResult.ReadXml(resultXMLReader);
            dsResult.AcceptChanges();
            resultXMLReader.Close();
            Console.WriteLine("Contact Email: " + dsResult.Tables[0].Rows[0]["ContactEmail"]);
            Console.WriteLine("Name         : " + dsResult.Tables[0].Rows[0]["Name"]);
            Console.WriteLine("Number       : " + dsResult.Tables[0].Rows[0]["Number"]);
            Console.ReadLine();

            Dts.Variables["sCustomerEmailAddress"].Value = dsResult.Tables[0].Rows[0]["ContactEmail"];
            Dts.Variables["sCustomerName"].Value = dsResult.Tables[0].Rows[0]["Name"];
            Dts.Variables["sCustomerNumber"].Value = dsResult.Tables[0].Rows[0]["Number"];


            //Dts.Variables["sCustomerCode"].Value.ToString();

            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Open in new window

Christopher Kile

Then this code should adapt perfectly. You avoid XPATH queries and virtually all parsing of the XML itself. If, by chance, you have a schema against which to validate this XML, you can read it into the DataSet just prior to reading the XML itself, though with a simple flat structure like this the method I supplied you is probably the simplest way of extracting the values.

If you're going to encrypt then store the table, note this next example, where I read a second XML file into the same dataset. Note that it added a new row to the existing table rather than overwriting the existing dataset completely. The loop shows how to iterate through the rows of the data set, demonstrates how to modify each row and display it, and uses .AcceptChanges to insure the modfications are permanent, leaving to you whether you create a DataAdapter that will update the database for you or whether you do it yourself with SqlConnection, SqlCommand, etc. Here is the second example:

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;

namespace XMLDataSetGetSelectedValues
{
    class Program
    {
        static void Main(string[] args)
        {
            string strResult = "<ArrayOfCustomerAccount>"
                            + "<CustomerAccount>"
                            + "<ContactEmail> whacka@doo.com</ContactEmail> "
                            + "<Name>Michael Stipe</Name>"
                            + "<Number>42</Number>"
                            + "</CustomerAccount>"
                            + "</ArrayOfCustomerAccount> ";

            string strResult2 = "<ArrayOfCustomerAccount>"
                            + "<CustomerAccount>"
                            + "<ContactEmail>pot.r@z.be</ContactEmail> "
                            + "<Name>Marvin Neuman</Name>"
                            + "<Number>69</Number>"
                            + "</CustomerAccount>"
                            + "</ArrayOfCustomerAccount> ";

            // XmlTextReader resultXMLReader = new XmlTextReader(x.GetResponseStream());

            
            XmlTextReader resultXMLReader = new XmlTextReader(new StringReader(strResult));
            DataSet dsResult = new DataSet();

            dsResult.ReadXml(resultXMLReader);
            dsResult.AcceptChanges();
            resultXMLReader.Close();

            resultXMLReader = new XmlTextReader(new StringReader(strResult2));

            dsResult.ReadXml(resultXMLReader);
            dsResult.AcceptChanges();
            resultXMLReader.Close();

            for (int i = 0; i < dsResult.Tables[0].Rows.Count; i++)
            {
                dsResult.Tables[0].Rows[i]["ContactEmail"] += "; email mod";
                dsResult.Tables[0].Rows[i]["Name"] += "; name mod";
                dsResult.Tables[0].Rows[i]["Number"] += "; name mod";

                Console.WriteLine("Contact Email: " + dsResult.Tables[0].Rows[i]["ContactEmail"]);
                Console.WriteLine("Name         : " + dsResult.Tables[0].Rows[i]["Name"]);
                Console.WriteLine("Number       : " + dsResult.Tables[0].Rows[i]["Number"]);
            }
            dsResult.AcceptChanges();
            Console.ReadLine();
        }
    }
}

Open in new window

You can also initliaze the XMLTextReader straight from .GetResponsStream() (it's one of the overloads of .ReadXML()) but I leave that as an exercise for you if you want to do it that way.

Christopher Kile

LOL our comments passed each other. Glad to help.