February 21, 2013 at 6:54 am
Hello,
has anyone used SSIS to convert currency based on a date from a table?
For example;
If I have a table with the following Columns;
Amount Currency USDAmount InvoiceDate
1000 CAD NULL NULL
I would like to possibly use a free web service that will look at my Invoice Date and do the Math based on the Spot currency rate for that time and return the converted US Dollar amount.
Thanks
February 21, 2013 at 12:47 pm
My WSDL is not that strong but I would go along the lines of using a dataflow in the format:
Data source to get your data which feeds a script component synchronous transform which:
Set the input USDAmount column to read/write
In the PreProcess method sets up the WSDL
In the ProcessInputRow method pass the date, currency code and amount to the WSDL and uses the object attribute to apply the conversion to the currency value and store the result in the USDAmount column.
The performance will not be great since the web service will be called for each row.
If possible pre-sort your amounts by date and currency. This means that you can optimise the script component to only access the web service and get the exchange rate on a change of date and/or currency
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply