Capturing Real-Time Currency Conversions in SSIS

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

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

    Converting that into a table is also very simple.

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


    * Noel

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

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

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

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

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

  • 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

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

  • 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

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

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

  • 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

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

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

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