Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Capturing Real-Time Currency Conversions in SSIS

By Jeff Singleton,

Overview

With international commerce growing every day, the means by which funds are exchanged grows as well. From exchange rate calculations to international banking/trading and companies that do business internationally the need to convert currencies in real time, is becoming a necessity in the global marketplace. In a typical foreign exchange transaction, a party purchases a quantity of one currency by paying a quantity of another currency using floating exchange rates.

In this article, I will demonstrate a data flow solution for converting Canadian currency using real time floating exchange rates through the use of a web service task, a record set result, and parameter driven dynamic SQL.

Implementation

First let’s take a look at the data to be converted. Here we have a SQL table with an amount and a currency code. The plan is to convert the Canadian dollar (CAD) amount into US dollars (USD). To capture the real time conversion rate, we will first need to call on a Web Service Task.

Table of data, before conversion

Once you have imported the Web Service Task into the control flow, you will need to configure it. I am going to use a handy site (http://www.webservicex.net) that provides free access to web services. Using WSDL (Web Services Description Language) which is an XML-based language that provides a model for describing Web services, we are able to call on this web service to get the floating exchange rate.

Connection Manager for http

When configuring the web service task, click on HttpConnection and ‘New Connection’. At the Http Connection Manager screen, copy and paste the full path to the web service WSDL. Also copy and paste that same link into a browser window and click file and ‘Save As’ to save the physical WSDL file to your local machine. Back at the Web Service Task Editor, Import the WSDL file that you just saved from the browser window to your local machine as the WSDLFile on the Web Service Task Editor.

 

web service properties

On the Input tab, you will see a service drop down; choose CurrencyConvertor. Under Method choose ConversionRate. On the pane below that, you will be able to select the currency that you want to convert from to the currency that you want to convert to. In this example, I am converting from CAD to USD.

 

web service task propertiesOn the Output tab, choose File Connection and create a simple XML file to hold the output of the conversion. 

web service task, output properties

 

Next, create a Data Flow Task. We will use this data flow task to dynamically parse the exchange rate and store it in a Record Set destination. In the Data Flow Task, drag and drop a Flat File Source and choose a new Flat File Connection Manager. When configuring the Flat File Connection Manager, choose the XML file that you are using to hold the output of the currency conversion. Normally, you could use an XML source as the file but to show some more complex ETL, I am going to read this data in as a flat file and parse the exchange rate in a Derived Column Transformation using logic that will dynamically determine the length of the conversion rate. Under file format, choose ragged right and also select 1 header row to skip so that we are only going to read the data in the file and not the XML header. Since there is only 1 record and 1 column, you will only have 1 column in the advanced tab that we will call ‘ConversionRate’.

 

Flat File connector properties

Now, drag and drop a Derived Column transformation and connection it to the flat file source. Here, you will have a single Derived column to capture only the conversion rate. Using substring and findstring, you can dynamically parse the field to take everything from the 9th byte (where the field starts) to the character “</” which denotes the end of the string. This way, if the field is 10 characters long or 40 characters long, we will only capture the conversion rate and no other data.

 

derived column properties

Next, connect a Data Conversion Transformation to your data flow and convert the ConversionRate field to Output as ConversionNumeric as a data type DT_NUMERIC with a precision of 18 and a scale of 6.

 

data conversion transformation properties

As the last part of the Data Flow, we are going to store the conversion rate in a Record Set Destination. A Record Set Destination creates and populates and in-memory ADO recordset. We can then utilize this recordset later in the package. Under the Recordset Destination Editor, create a new variable called ExecutedConversion at the package scope.

 

destination recordest properties

Your only input column will be the ConversionNumeric field that you created in the data conversion transform.

 

destination recordeset columns

Now, create a Foreach Loop in your control flow connected to your data flow task. Under the connection tab, change the Enumerator to a ‘Foreach ADO Enumerator’. Choose the Recordset variable ‘User::ExecutedConversion’ as the ADO object source variable and ‘Rows in the first table’ as your Enumeration mode.

for each loop editor

On the Variable Mappings tab, again select the variable ‘User::ExecutedConversion’ and set the Index value equal to 0. These two steps will capture the conversion rate and enumerate (or sequentially list) and pass the values captured into the Foreach Loop.

 

for each loop variable mapping

Once the Foreach Loop has been configured, drag and drop an Execute SQL task into the Foreach Loop. Set your connection to the database that has the table with the amounts to be converted, set BypassPrepare equal to true and use the below code as your SQLStatement:

UPDATE CurrencyConversion
SET USD_Amount = Amount * ?,
ConvertedDate = getdate()
WHERE USD_Amount IS NULL

Execute SQL Task

The question mark in the SQL statement will use a value passed to the Parameter Mapping of the Execute SQL Task. Select ‘User::ExecutedConversion’ as the variable name with a data type of FLOAT , a Parameter Name of 0 and a Parameter Size as -1.

Execute SQL Task, parameter mapping

Now, execute your package and check your table to verify that the currency conversion has worked properly.

Package execution

table data, after conversion

Conclusion

In this article, I’ve demonstrated how to use SQL Server Integration Services (SSIS) to create a process that can capture real-time currency conversion rates, parse the conversion rate dynamically with the use of a derived column transformation and pass the conversion rate to a record set to be used in a SQL task.

 

Total article views: 6539 | Views in the last 30 days: 7
 
Related Articles
FORUM

Trying to understand Currency conversion

Trying to understand Currency conversion

BLOG

Currency Conversion in Tabular Model Using DAX

Roughly a year ago, I implemented currency conversion logic in multidimensional cubes using measure ...

BLOG

SSAS: Currency Conversion in MDX Script

In my prior blog post, I provided some details regarding currency conversion via measure expressions...

BLOG

SSAS: Currency Conversion Using Measure Expressions

Currency conversions can be implemented several ways as part of a business intelligence solution.  I...

FORUM

format the number in indian currency format

format the number in indian currency format

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones