Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Capturing Real-Time Currency Conversions in SSIS Expand / Collapse
Author
Message
Posted Wednesday, April 28, 2010 10:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:05 AM
Points: 27, Visits: 147
Comments posted to this topic are about the item Capturing Real-Time Currency Conversions in SSIS
Post #912511
Posted Thursday, April 29, 2010 3:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 30, 2010 5:47 AM
Points: 4, Visits: 25
What about just consuming http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml ?
Converting that into a table is also very simple.




Post #912634
Posted Thursday, April 29, 2010 7:28 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
what exactly is "real-time" about this ?


* Noel
Post #912825
Posted Thursday, April 29, 2010 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 11, 2014 8:11 AM
Points: 109, Visits: 608
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.
Post #912931
Posted Thursday, April 29, 2010 11:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 2, 2013 8:57 AM
Points: 7, Visits: 11
Great Article! How often are you processing/do you recommend processing the conversion rate?
Post #913116
Posted Thursday, April 29, 2010 11:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:05 AM
Points: 27, Visits: 147
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.
Post #913125
Posted Thursday, April 29, 2010 12:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 8, 2014 10:05 AM
Points: 27, Visits: 147
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.
Post #913173
Posted Thursday, April 29, 2010 12:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 25, 2010 6:40 AM
Points: 46, Visits: 133
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.
Post #913199
Posted Thursday, April 29, 2010 1:28 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 18, 2010 8:24 AM
Points: 12, Visits: 85
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
Post #913219
Posted Thursday, April 29, 2010 1:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 5:26 AM
Points: 1,120, Visits: 4,909
I must be missing something - what does this have to do with SSIS? It looks more like web development? Can anyone explain please
Post #913225
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse