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
Author
Message
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: Friday, February 14, 2014 10:54 AM
Points: 91, Visits: 475
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



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

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

Group: General Forum Members
Last Login: Wednesday, October 02, 2013 1:49 AM
Points: 116, Visits: 289
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