Capturing Real-Time Currency Conversions in SSIS

  • ava1over

    Old Hand

    Points: 383

    Comments posted to this topic are about the item Capturing Real-Time Currency Conversions in SSIS

  • dewit.john

    Valued Member

    Points: 56

    What about just consuming http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml ?

    Converting that into a table is also very simple.

  • noeld

    SSC Guru

    Points: 96590

    what exactly is "real-time" about this ? 🙁


    * Noel

  • Phil Brammer

    Ten Centuries

    Points: 1135

    noeld (4/29/2010)


    what exactly is "real-time" about this ? 🙁

    Each time the package is executed, it should download the most current exchange rates.

    In something like batch processing, you wouldn't want the conversion rate to change, in "real-time" for each record in your source anyway - you'd want them all to have the same conversion rate timestamp so that the data is consistent for the given source population.

    As far as feedback goes, the screenshots and associated steps seemed quite hard to follow and out of order a bit. I'll have to go back and re-read it when I have some more time.

  • bob_006

    SSC Rookie

    Points: 49

    Great Article! How often are you processing/do you recommend processing the conversion rate?

  • ava1over

    Old Hand

    Points: 383

    dewit.john (4/29/2010)


    What about just consuming http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml ?

    Converting that into a table is also very simple.

    Hey John, thanks for the reply. This is really good data and to your point could be ingested and refreshed every day.

  • ava1over

    Old Hand

    Points: 383

    bob_006 (4/29/2010)


    Great Article! How often are you processing/do you recommend processing the conversion rate?

    Thanks for the feedback Bob. In regards to your question: it depends. Like Phil Mentioned, if you are doing batch processing you wouldn't want your conversion rate to change. Most e-tailers (amazon included) are updating their conversion rates daily.

    Hope that helps.

  • jbuttery

    Ten Centuries

    Points: 1196

    We do live in a 24 hour world. Of course, if you can buy and sell a currency at the same price throughout your business day (assuming no commission), there shouldn't be a problem.

  • richard.maw

    SSC Veteran

    Points: 226

    There may be other motives for the method shown that I'm not getting but it seems to be an insanely inefficient and complicated way to do it: read service, write to file, read file, parse text, convert text, wrap it in dataset, iterate over dataset and then run the update. You can do it in two steps (1) read service into text variable (2) pass the text variable to the update statement. The parsing is done in SQL like this:

    Update CurrencyConversion

    Set USD_Amount = Amount * convert(xml,?).value('(//double)[1]','float'),

    ConvertedDate = getdate()

    Where USD_Amount IS NULL

  • jts2013

    Hall of Fame

    Points: 3226

    I must be missing something - what does this have to do with SSIS? It looks more like web development? Can anyone explain please 🙂

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ava1over

    Old Hand

    Points: 383

    richard.maw (4/29/2010)


    There may be other motives for the method shown that I'm not getting but it seems to be an insanely inefficient and complicated way to do it: read service, write to file, read file, parse text, convert text, wrap it in dataset, iterate over dataset and then run the update. You can do it in two steps (1) read service into text variable (2) pass the text variable to the update statement. The parsing is done in SQL like this:

    Update CurrencyConversion

    Set USD_Amount = Amount * convert(xml,?).value('(//double)[1]','float'),

    ConvertedDate = getdate()

    Where USD_Amount IS NULL

    Good point and very true. As with any exercise I wanted to show off some of the other capabilities that SSIS has... some for my benefit and hopefully for the benefit of a few others.

  • rmaw-1095848

    SSC Rookie

    Points: 28

    Fair enough, I learned something myself. But if someone is just looking for the functionality provided the two-step method is the way to go.

  • nba.ahamed

    Grasshopper

    Points: 12

    Hi,

    Good articles, but it is not working for me

    I got below error message in RecordSet Destination

    Error at Data Flow Task [RecordSet Destination ..]: The type of the runtime variable "User:ExecutedConversion" is incorrect. The runtime variable type must be Object

    please assists.

    Bahru

  • sharath.chalamgari

    SSCertifiable

    Points: 5680

    For this Error please go the variable section and change the DataType to Object.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply