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

Convert Currency in SSIS Expand / Collapse
Posted Thursday, February 21, 2013 6:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 11:02 AM
Points: 98, Visits: 532

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


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.


Post #1422555
Posted Thursday, February 21, 2013 12:47 PM


Group: General Forum Members
Last Login: Friday, March 27, 2015 10:45 AM
Points: 118, Visits: 297
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
Post #1422758
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse