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

unclepaul84 · Accepted Answer · 2010-09-20 20:25:49Z

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

edited Sep 20, 2010 at 20:25

answered Sep 20, 2010 at 19:49

unclepaul84

1,4048 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jack Over a year ago

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).

jack Over a year ago

Thanks you so much UnclePaul. You're awesome.. :) I've been trying to figure out the way to load the data in a table. It's working now. I really really appreciate your help. Have a great day.

adopilot · Accepted Answer · 2010-09-20 20:44:54Z

0

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

Here is basic steps.

Make DTS package
In DataFlow select source connection XML and fill up form abaout source and VXD (structure) file
Then make destination connection
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 ?

answered Sep 20, 2010 at 20:44

adopilot

4,58015 gold badges69 silver badges96 bronze badges

Collectives™ on Stack Overflow

Writing XML data from URL to Object in SSIS Script Task

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related