Writing XML data from URL to Object in SSIS Script

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

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 ?