Sunday, November 6, 2011

SSIS: How to pull Currency Rates from European Central Bank

This post shows how you can use European Central Bank daily currency feed to pull the Currency Rates in SSIS without using any web services.

Each country currency has different values. When you visit a particular country one of the important thing you want to know is what is the currency and what the currency rate is.

European Central Bank is one of many sources which publish the currency rates every day. The currency rates are published in Euro. If you visit their daily currency rates feed by navigating the following URL; you will find the currency rates in 33 different currencies.

http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml




You can use the above European Central Bank daily feed in SSIS to pull the currency rates.

For the demonstration purpose I have create a table which has following three columns (Currency, Rate and LastUpdatedTime). This table stores the daily currency rates feed which is pulled from the above mentioned ECB URL.

To pull the currency rates from ECB daily I created one OLE DB connection in my SSIS project which points to the Database which has the above table. I created following task under the Control Flow tab.


The Execute SQL Task simply truncate all the rows from the SQL table


The Data Flow Task has following components


I created a package level variable to store the ECB daily feed URL.

The script component under Data Flow tab is a source. I have setup the ReadOnlyVariable values to User::ECBRL. I have added three output columns in inputs and outputs. The three output columns are Currency, Rates and LastUpdate.

I have following codes in the script component.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Xml;

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

    public override void PreExecute()
    {
        base.PreExecute();        
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        string strCurrency;
        string flRate;
        XmlDocument xm = new XmlDocument();
        xm.Load(Variables.ECBRL.ToString());

        XmlNodeList xnode = xm.GetElementsByTagName("Cube");
        
        foreach (XmlNode xmn in xnode)
        {
            XmlElement currencyElement = (XmlElement)xmn;
            if (currencyElement.HasAttributes)
            {                
                strCurrency = currencyElement.GetAttribute("currency").ToString();
                flRate =currencyElement.GetAttribute("rate");
                if (strCurrency.Length > 0)
                {
                    Output0Buffer.AddRow();
                    Output0Buffer.Currency = strCurrency;
                    Output0Buffer.Rate = flRate;
                    Output0Buffer.LastUpdate = DateTime.Now;
                }
            }

        }

        }


As you can see in the code we are pulling the Currency rates of each of the currency listed in the ECB feed using Syste.XML namespace.

The OLE DB Destination points to our table which is storing the Currency rates. I have mapped the Script Component output columns to the SQL table columns.

After setting up the Data Flow Task components I connected with the internet and run the package. It runs successfully and pulls the Currency rates values and updated them in the table.

This post shows one of the ways we can use to pull the Currency rates from one of the most trusted source i.e. European Central Bank. There are many ways to accomplish the same in SSIS.

I hope you enjoy reading the post. Thanks for the reading.

Related Article:
Labels: , ,

Popular Posts

Real Time Web Analytics