Capturing Real-Time Currency Conversions in SSIS

Question

Post reply

Capturing Real-Time Currency Conversions in SSIS

ava1over

Old Hand

Points: 383
More actions
April 28, 2010 at 10:06 pm

#232132

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

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

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

dewit.john Valued Member Points: 56 More actions · Answer 1

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 More actions · Answer 2

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

* Noel

Phil Brammer Ten Centuries Points: 1135 More actions · Answer 3

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 More actions · Answer 4

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

ava1over Old Hand Points: 383 More actions · Answer 5

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 More actions · Answer 6

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: 1226 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

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: 281334 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

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 More actions · Answer 14

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