Writing XML data from URL to Object in SSIS Script Task

Question

I have this URL where there is XML data. I have to extract that data from URL and dump it into DW table. I am using SSIS Script Task for that.

This is how the data looks like:

-<currency>

<csymbol>AED</csymbol>

<cname>United Arab Emirates Dirhams</cname>

<crate>3.6732001305</crate>

<cinverse>0.2722421770</cinverse>

</currency>

−<currency>

<csymbol>AFN</csymbol>

<cname>Afghanistan Afghanis</cname>

<crate>44.0000000000</crate>

<cinverse>0.0227272727</cinverse>

</currency>

−<currency>

<csymbol>ALL</csymbol>

<cname>Albania Leke</cname>

<crate>104.4100000001</crate>

<cinverse>0.0095776267</cinverse>

</currency>

This is the code i'm using to load it into some Object type or something. But i dont know how to do that.

public void Main()
{
    String URLString = "http://www.xe.com/dfs/datafeed2.cgi?beeline";
    XmlDocument doc = new XmlDocument();
    XmlTextReader reader = new XmlTextReader(URLString);
    doc.Load(reader);

    XmlNodeList currencynodes = doc.SelectNodes("currency");
    foreach(XmlNode currency in currencynodes)
    {
        XmlNode csymbol = currency.SelectSingleNode("csymbol");
        string csymbolvalue = csymbol.Value;

        XmlNode cname = currency.SelectSingleNode("cname");
        string cnamevalue = cname.Value;

        XmlNode crate = currency.SelectSingleNode("crate");
        string cratevalue = crate.Value;

        XmlNode cinverse = currency.SelectSingleNode("cinverse");
        string cinversevalue = cinverse.Value;

        Dts.Variables["User::oCurrencyConversion"].Value = csymbol.Value;
    }

Answer 1

You actually need to use a Script Source Component inside of a dataflow task for this. Then use the standard destination compoents to do the insert into DW.

Here is a sample package i implemented. http://dl.dropbox.com/u/5332312/xRateLoader.zip

Answer 2

I tried that too but basically my main problem is I am not at all c# coding person. Based on a few articles online it took me like 2 days to write this few lines of code. And that's why I need some help to transform that XML into a DB table (or object variable) where i will have 4 columns (symbol, name, rate, inverse).

It'll be really helpful if you can provide few lines of code which i can start off of.

Answer 3

Once I did the same thing pulling data from XLM to SQL trough SSIS package.

Here is basic steps.

1. Make DTS package
2. In DataFlow select source connection XML and fill up form abaout source and VXD (structure) file
3. Then make destination connection
4. Pull green line from Source to Destination

You need to pay attention to VXD is right format, so SSIS can read data preply.

Somewhere in my office desktop I still have source of that SSIS Package, first thing in morning I am going to try find that and share here.
Also If is source of your XML is public please post here so we can try make one for you.
Aru You preferred more to do this task usin C# or integration service ?

Answer 4

UnclePaul, the SSIS package with script you provided, for the XML in above question, worked awesome for that XML Structure. This is what you had in ScriptSourceComponenet.

using (XmlTextReader reader = new XmlTextReader(URLString))
    {
        doc.Load(reader);

        XmlNodeList currencynodes = doc.SelectNodes("//currency");

        foreach (XmlNode currency in currencynodes)
        {
            XmlNode csymbol = currency.SelectSingleNode("csymbol");
            XmlNode cname = currency.SelectSingleNode("cname");
            XmlNode crate = currency.SelectSingleNode("crate");
            XmlNode cinverse = currency.SelectSingleNode("cinverse");

            Output0Buffer.AddRow();

            float inverse = 0;
            float rate = 0;
            float.TryParse(cinverse.InnerText, out inverse);
            float.TryParse(crate.InnerText, out rate);
            Output0Buffer.Inverse = inverse;
            Output0Buffer.Rate = rate;
            Output0Buffer.Name = cname.InnerText;
            Output0Buffer.Symbol = csymbol.InnerText;

        }
    }

But i have new XML structure below where XML Nodes are kind of odd structured.

For ADF - 7.7788 and for ADP - 196.230

-<RESPONSE>
  <csymbol>ADF</csymbol>
 −<CONVERSION>
   <crate>7.7788</crate>
 </CONVERSION>
 <csymbol>ADP</csymbol>
−<CONVERSION>
   <crate>196.230</crate>
 </CONVERSION>
</RESPONSE>

In that case, how am i going to get XMLNodeList and then use foreach XMLNode? I know i am missing something very simple in the Script. Hope you can help me with this like you did last time. :)

Appreciate your help, UnclePaul