BizTalk Application: Currency Exchange Rates

Exclusive offer: get 50% off this eBook here
Microsoft BizTalk 2010: Line of Business Systems Integration

Microsoft BizTalk 2010: Line of Business Systems Integration — Save 50%

A practical guide to integrating Line of Business systems with Microsoft BizTalk Server 2010 in this book and eBook

$35.99    $18.00
by Carl Darski | August 2011 | Enterprise Articles

The best way to understand the behavior and setup required to use the Dynamics AX BizTalk adapter is to walk through a full example. A common requirement for many companies that use Dynamics AX is to populate the currency exchange rates. Typically, this needs to be daily when the closing rates are published. The Exchange Rates(General ledger | Setup Exchange rates) table in AX is not a shared table across companies, thus depending how your organization is set up; you may or may not need to populate the Exchange rate table using multiple companies with multiple rates.

In this article by Carl Darski, author of Microsoft BizTalk 2010: Line of Business Systems Integration, we'll create a BizTalk application to populate the exchange rates table.

 

Microsoft BizTalk 2010: Line of Business Systems Integration

Microsoft BizTalk 2010: Line of Business Systems Integration

A practical guide to integrating Line of Business systems with BizTalk Server 2010

        Read more about this book      

(For more resources on BizTalk, see here.)

We are going to assume that we have two companies in our Dynamics AX implementation; one that is Canadian Dollar (CAD) based, and one that is United States Dollar(USD) based. Thus, we need to use the LedgerExchangeRates.create and LedgerExchangeRates.find actions in both companies. For the remainder of this example, we'll refer to these as daxCADCompany and daxUSDCompany. The complete solution, titled Chapter9-AXExchangeRates, is included in the source code.

 

Dynamics AX schemas

We'll start by creating a new BizTalk project, Chapter9-AXExchangeRates, in Visual Studio. After the AIF actions setup is complete, the next step is to generate the required schemas that are needed for our BizTalk application. This is done by right clicking on the BizTalk project in Visual Studio 2010, click Add, highlight and click Add Generated Items. This will bring up the Add Generated Items window, under the Templates section—Visual Studio installed template, select Add Adapter Metadata, and click Add. This will bring up the Add Adapter Wizard window (shown in the following screenshot), so we'll simply select Microsoft Dynamics AX 2009 and click Next. Now, we'll need to fill in the AX server instance name (AX 2009-SHARED in our example) under Server name, and the TCP/IP Port number (2712, which is the default port number, but this can differ). Now, click Next from the BizTalk Adapter for Microsoft Dynamics AX Schema Import Wizard window.

Microsoft BizTalk 2010: Line of Business Systems Integration

Specify the connection information in the next step.

Microsoft BizTalk 2010: Line of Business Systems Integration

In the next window, you should see all the active AIF services. Note that since the AIF services table is a global table, so you will see all the active services in your Dynamics AX instance. This does not mean that each endpoint, thus each company, is configured to accept the actions that each AIF service listed has available. This is the point where you first verify that your connectivity and AIF setup is correct. An error here using the wizard typically is due to an error in the AIF channel configuration.

Microsoft BizTalk 2010: Line of Business Systems Integration

In the wizard window above, you can see the AIF services that are enabled. In our case, the ExchangeRatesService is the only service currently enabled in our Dynamics AX instance. Under this service, you will see three possible modes (sync, async request, and async response) to perform these actions. All three will actually produce the same schemas. Which mode and action (create, find, findkeys, or read) we use is actually determined by the metadata in our message we'll be sending to AX and the logical port configurations in our orchestration. Now, click Finish.

Now in the project solution, we see that the wizard will generate the two artifacts. The first ExchangeRates_ExchangeRates.xsd is the schema for the message type that we need to send when calling the LedgerExchangeRates.create action and it is also the same schema returned in the response message when calling the action LedgerExchangeRates.find. Since we are actually dealing with the same AX table in both actions, Exchange Rates, both actions will in part (one will be the inbound message, the other will be the outbound message) require the same schema.

The second artifact, BizTalk Orchestration.odx, is also generated by default by the wizard. In the orchestration view, we can see that four Multi-part Message Types were also added to the orchestration. Rename the orchestration to something more meaningful such as ProcessExchangeRates.odx.

Now that we have defined our message type that will be returned in our response message, we need to define what the request type will be. Notice from the orchestration view that two messages, ExchangeRatesService_create_Response and ExchangeRatesService_find_Request, have types which Visual Studio has in error 'does not exist or is invalid'.

Microsoft BizTalk 2010: Line of Business Systems Integration

For the out-of-the-box find action, we need the message type DynamicsAX5.QueryCriteria. The other message type is return by AX when calling a create action is DynamicsAX5.EntityKey (if we called a createList action, the returned message would be of type DynamicsAX5.EntitiyKeyList).

The schemas for these message types are in the Microsoft.Dynamics.BizTalk.Adapter.Schemas assembly, which can be found in the bin directory of the install location of the BizTalk adapter. Add this reference to the project in Visual Studio. Then, re-select the appropriate message type for each Message Part that is invalid from the Select Artifact Type window as shown.

Microsoft BizTalk 2010: Line of Business Systems Integration

Next, depending on your organization, typically you may want to either populate the noon exchange rates or closing rates. For our example, we will use the closing USD/CAD exchange rates from the Bank of Canada. This is published at 16:30 EST on the website (http://www.bankofcanada.ca/rss/fx/close/fx-close.xml).

Since this source is already in XML, download and save a sample. We then generate a schema from Visual Studio using the BizTalk Schema Generator (right click the solution, Add Generated Items, Add Generated Schemas, using the Well-Formed XML (Not Loaded) document type. This will generate the schema for the message that we need to receive by our BizTalk application daily. In the example provided, the schema is ClosingFxRates.xsd (the wizard will generate four other .xsd files that are referenced in ClosingFxRates.xsd).

A simple way to schedule the download of this XML data file is to use the Schedule Task Adapter (http://biztalkscheduledtask.codeplex.com/), which can be downloaded and installed at no cost (the source code is also available). Download and install the adapter (requires Microsoft .NET Framework Version 1.1 Redistributable Package), then add using the BizTalk Server Administration Console with the name Schedule. We will use this adapter in our receive location to retrieve the XML via http. There are also RSS adapters available to be purchased from, for example, /nsoftware (http://www.nsoftware.com/). However, for this example, the scheduled task adapter will suffice.

Now, since the source of our exchange rates is a third-party schema, and your specific requirements for the source will most likely differ, we'll create a canonical schema ExchangeRates.xsd. As you can see in the schema below, we are only interested in a few pieces of information: Base Currency (USD or CAD in our example), Target Currency (again USD or CAD), Rate, and finally, Date. Creating a canonical schema will also simplify the rest of our solution.

Microsoft BizTalk 2010: Line of Business Systems Integration

Now that we have all the schemas defined for our message types defined or referenced, we can add the messages that we require to the orchestration.

We'll begin by adding the message msgClosingFxRates. That will be our raw input data from the Bank of Canada with the message type from the generated schema ClosingFxRates.RDF.

For each exchange rate, we'll need to first query Dynamics AX to see if it exists, thus we'll need a request message and a response message. Add a message msgAXQueryExchangeRatesRequest, which will be a multi-part message type ExchangeRatesService_find_Request, and msgAXQueryExchangeRatesResponse that will be a multi-part message type ExchangeRatesService_find_Response.

Next, we'll create the messages for the XML that we'll send and receive from Dynamics AX to create an exchange rate. Add a message msgAXCreateExchnageRatesRequest, which will be a multi-part message type of ExchangeRatesService_create_Request, and msgAXCreateExchnageRatesResponse that will be a multi-part message type ExchangeRatesService_create_Response.

Finally, we'll need to create two messages, msgExchangeRatesUSDCAD and msgExchangeRatesCADUSD, which will have the message type of the canonical schema ExchangeRates. These messages will contain the exchange rates for USD to CAD and for CAD to USD respectively. We'll create these two messages just to simplify our orchestration for this example. In practice, if you're going to deal with several exchange rates, you will need to add logic to the orchestration to loop through the list rates that you're interested in and have only one message of type ExchangeRates resent several times.

 

Microsoft BizTalk 2010: Line of Business Systems Integration A practical guide to integrating Line of Business systems with Microsoft BizTalk Server 2010 in this book and eBook
Published: July 2011
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

 

        Read more about this book      

(For more resources on BizTalk, see here.)

We'll need to know the Dynamics AX company name in order to populate the metadata in our message sent to AX with the correct source and destination endpoint. For this example, we'll hard code these two company names with daxCADCompany and daxUSDCompany variables of type System.String. We'll add a variable, needToLoadFxRateUSD, of type System.Boolean that we'll use in our orchestration. Also, we'll hard code the variable serviceAccountName with the Active Directory service account that our BizTalk host instance is running under. Finally, we need a variable named xpathQueryCADResultsExpression of type System.String that we'll use to determine our query response message. The following is an orchestration view on the messages and variables:

Microsoft BizTalk 2010: Line of Business Systems Integration

Now, we'll need to add a logical receive port and two logical send ports in our orchestration.

Create a new logical receive port for the XML closing exchange rates from the Bank of Canada with the following settings:

Port Name ReceiveClosingFxRates_Port
Port Type Name ReceiveClosingFxRates_PortType
Communication Pattern One-Way
Port direction of communication I'll always be receiving messages on this port
Port Binding Specify Later

Create a new send-receive logical port to Query Dynamics AX with the following settings:

Port Name QueryAXExchangeRates_Port
Port Type Name QueryAXExchangeRates_PortType
Communication Pattern Request-Response
Port direction of communication I'll be sending a request and receiving a response
Port Binding Specify Later

Create a new second send-receive logical port to send the exchange rates create message with the following settings:

Port Name CreateAXExchangeRates_Port
Port Type Name CreateAXExchangeRates_PortType
Communication Pattern Request-Response
Port direction of communication I'll be sending a request and receiving a response
Port Binding Specify Later

Next, we'll need to create two maps to transform the Bank of Canada XML message to our canonical exchange rates messages. See the details for these two maps in the code example provided.

  • ClosingFxRates_To_ExchangeRatesCADUSD.btm
    • CAD Base Currency—USD Target Currency
  • ClosingFxRates_To_ExchangeRatesUSDCAD.btm
    • USD Base Currency—CAD Target Currency

Again, to limit the complexity of this example, we are somewhat hard-coding these two maps for our specific needs. If we needed to create many messages with different base/target currency combinations, then we would add logic to our orchestration and create a more complex map.

Now, we'll need to create two more maps to transform from our canonical ExchangeRates schema to (1) Query Dynamics AX for the particular rate and (2) ExchangeRatesService_ExchangeRates AIF schema generated by the BizTalk Dynamics AX adapter.

Dynamics AX query message

TheDynamicsAx5.QueryCriteria schema is shown in our map ExchangeRates_To_AXQueryExchangeRatesRequest.btm. Each company in Dynamics AX will have a base currency, thus all exchange rates will be relative to that base. For each date, there can only exist one rate for a specific target currency, thus we'll construct a query and filter specifically for the date. Our query will return all exchange rates for a specific date with many target currencies, so we'll write an XPath expression to find the one we're interested in.

In the map above, we'll use String Concatenate functoids to store constants that we need to populate the DataSoureName, FieldName, and Operator elements of the QueryCritera schema. The DataSouce is the Dynamics AX 2009 table name, ExchRates. The FieldName is the column, the table, FromDate. Finally, the Operator we'll set to Equal.

Dynamics AX create message

As shown in the map ExchangeRates_To_AXCreateExchangeRatesRequest.btm, the transform is relatively simple. Dynamics AX exchange rates are based on a scale relative to 100 rather than 1, thus we'll need to multiply the value from our ExchangeRates schema by 100 using the Multiplication functoid as shown below. Notice that we can use this transform for any Base/Target currency combination.

Also, notice that there are no dimensions in the ExchangeRates Dynamics AX schema. Typically, in most business-orientated messages, such as Sales Order, the schema would contain a single element for the dimensions. In this case, we would recommend first adding the exact number of String Concatenate functoids as dimension in your map and add links to the element field under the Dimension record in order from top to bottom in your Dynamics AX schema as shown below:

Microsoft BizTalk 2010: Line of Business Systems Integration

In each String Concatinate functoid as shown above, add zero length string input, and then add in the appropriate mapping logic for each dimension as an input to the relative concatenate functoid. This will ensure your output message will have the correct number of dimension elements and in the correct order, each time you add/delete/change a link in your map. To keep your maps readable, as a standard practice, you may choose to create a new page for mapping dimensions.

Continuing to build our orchestration, we'll add a Receive shape at the top to receive the msgClosingFxRtes message from the Bank of Canada and set the Activate property to True. Next, we'll add two Construct shapes to construct canonical messages msgExchangeRatesUSDCAD and msgExchangeRatesCADUSD each containing a transform shape using ClosingFxRates_To_ExchangeRatesCADUSD.btm and ClosingFxRates_To_ExchangeRatesCADUSD.btm respectively.

Microsoft BizTalk 2010: Line of Business Systems Integration

Orchestration setup

Now, we'll add one more Construct shape to construct our first msgAXQueryExchangeRatesRequest message (this will be to query our USD-based Dynamics AX company). We'll add a Transform shape for ExchangeRates_To_AXQueryExchangeRatesRequest.btm and use the msgExchangeRatesUSDCAD as an input to the transform. After the transform shape, we'll need to add in Message Assignment shape to add out AIF metadata. The contents of the Message Assignment Shape are:

msgAXQueryExchangeRatesRequest(DynamicsAx5.MessageId) = System.String.
Format("{0:B}",System.Guid.NewGuid());
msgAXQueryExchangeRatesRequest(DynamicsAx5.Action) = "http://schemas.
microsoft.com/dynamics/2008/01/services/ExchangeRatesService/find";
msgAXQueryExchangeRatesRequest(DynamicsAx5.DestinationEndpoint) =
daxUSDCompany + "LocalEndpoint";
msgAXQueryExchangeRatesRequest(DynamicsAx5.SourceEndpoint) =
daxUSDCompany + "Endpoint";
msgAXQueryExchangeRatesRequest(DynamicsAx5.SourceEndpointUser) =
serviceAccountName;

This step is where we can see that our naming conversion in the AIF setup is very important. The AIF metadata in our message determines which SourceEndpoint and DestinationEndpoint (Endpoint and Localendpoint tables in the AIF setup respectively) this message is intended for. It also determines which AIF Action the message is executed against. The SourceEndpointUser must also explicitly set in the metadata for each message.

Now, we'll add in a send shape followed by a Receive shape to Send the msgAXQueryExchangeRatesRequest and receive the msgAXQueryExchangeRatesResponse messages. Now, connect the send and receive shapes to the QueryAXExchangeRates_Port port as shown in the falling image:

Microsoft BizTalk 2010: Line of Business Systems Integration

Next, we'll add in an expression shape. Since we'll receive ALL the exchange rates corresponding to a FromDate that we specified in our query message, we need to determine whether the USD-based company is a corresponding entry for the CAD currency. The contents of the expression shape to do this are:

xpathQueryCADResultsExpression = System.String.Format("/*[localname()='
ExchangeRates']/*[local-name()='ExchRates']/*[localname()='
CurrencyCode' and text()='{0}']", "CAD");
// If we find a result, then don't need to load
if(xpath(msgAXQueryExchangeRatesResponse.ReturnValue,
xpathQueryCADResultsExpression) != null)
{
needToLoadFxRate = false;
}

Since the query returns a list of exchange rates, we'll use XPath to quickly determine whether the rate for our base currency exists for the date. You'll probably notice that we've hard coded the CAD currency code in our xpathQueryCADResultsExpression. In a more complicated scenario with multiple target currencies, you'll need to add logic to find the corresponding currency code you're interested in. Also, since it's very possible that each day we won't have a rate for each currency (due to statutory holidays for example); you will need to include this logic for each currency/date combination.

Now, we'll add in a Decide shape based on the needToLoadFxRate Boolean variable we set in the expression shape. If we've found a rate for that date we'll do nothing, otherwise we'll construct the msgAXCreateExchangeRatesRequest message and send it to Dynamics AX.

In the branch of the decide shape where the rate was not found, we'll add in a Construct Message shape containing a transform followed by a Message Assignment shape. The map we'll use is ExchangeRates_To_AXCreateExchangeRatesRequest.btm with the msgExchangeRatesUSDCAD message as an input.

In the expression shape, we'll need again to add the AIF metadata. The contents are:

msgAXCreateExchangeRatesRequest(DynamicsAx5.MessageId) = System.
String.Format("{0:B}",System.Guid.NewGuid());
msgAXCreateExchangeRatesRequest(DynamicsAx5.Action) = "http://schemas.
microsoft.com/dynamics/2008/01/services/ExchangeRatesService/create";
msgAXCreateExchangeRatesRequest(DynamicsAx5.DestinationEndpoint) =
daxUSDCompany + "LocalEndpoint";
msgAXCreateExchangeRatesRequest(DynamicsAx5.SourceEndpoint) =
daxUSDCompany + "Endpoint";
msgAXCreateExchangeRatesRequest(DynamicsAx5.SourceEndpointUser) =
serviceAccountName;

Notice that only the DynamicsAX5.Action is different from the previous query message sent to AX. Also note the use of the daxUSDCompany variable here.

Now, add in a Send shape followed by a Receive shape to send the msgAXCreateExchangeRatesRequest and receive the msgAXCreateExchangeRatesResponse messages. Now, connect the send and receive shapes to the CreateAXExchangeRates_Port.

Microsoft BizTalk 2010: Line of Business Systems Integration

This completes the steps for sending a USD – CAD exchange rate to our USDbased Dynamics AX Company. To complete our example, we'll now add in the logic for the USD – CAD exchange rate to our CAD-based Dynamics AX Company. So, we'll repeat the above from the Orchestration Setup step using the msgExchangeRatesCADUSD and the daxCADCompany in place of msgExchangeRatesUSDCAD and daxUSDCompany. See the complete code example.

In the example source code provided, there's no error handling built into the orchestration. If there's an error an in sending a message to Dynamics AX, the port will throw an exception. Quite often, especially in versions of Dynamics AX prior to AX 2009, the error message can be quite generic or cryptic at times. Connectivity errors will have some meaningful error message; however, logical errors will not. This is because the AIF module is an external facing interface, so the detailed error message most helpful is found in the Exceptions (Basic | Periodic | Application Integration Framework | Exceptions) form in the AIF module.

Now that we can build and deploy the solution, the next step will be to set up our Port configuration in the BizTalk Server Administration Console.

 

Microsoft BizTalk 2010: Line of Business Systems Integration A practical guide to integrating Line of Business systems with Microsoft BizTalk Server 2010 in this book and eBook
Published: July 2011
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

 

        Read more about this book      

(For more resources on BizTalk, see here.)

 

Port configuration

We'll begin by creating a Static Solicit-Response Send Port and type in a Name of AXExchangeRates_SendReceivePort.

  1. We'll set the Type to Microsoft Dynamics AX 2009, and set the Send pipeline and Receive pipeline to XML Transmit and XML Receive respectively.

  2. Click Configure to bring up the Microsoft Dynamics AX 2009 Transport Properties window.
  3. Here, we'll select an Authentication Type to Host User from the drop-down list and set the Gateway User to our Active Directory service account that our BizTalk Host instance is running under. We'll set the AOS Port to 2712 and AOS Server to our Dynamics AX 2009 server name AX 2009-SHARED. We can leave the default Synchronous Timeout to 20 minutes.

    Microsoft BizTalk 2010: Line of Business Systems Integration

  4. Click Ok to save this configuration.
  5. Next, we'll continue with the port configuration by creating a One-way Receive Port, named ReceiveClosingFxRates_ReceivePort, with a receive location named ReceiveClosingFxRates_ReceiveLocation. We'll use a Type of Schedule and set the Receive pipeline to XML Receive.

  6. Next, click Configure to bring up the Schedule Transport Properties window. Here, we'll type in a Name of Bank of Canada, and set the configuration Schedule to Daily Schedule with a Start Time of 5:00 pm (assume our server time is EST). Note we'll configure it to run daily, regardless of weekends or bank holidays (when no new rates are available), as our orchestration does the query to AX before attempting to create a new exchange rate.

    Microsoft BizTalk 2010: Line of Business Systems Integration

    Microsoft BizTalk 2010: Line of Business Systems Integration

  7. Now, click on the Task properties field form the Schedule Transport Properties window to bring up the Task Properties window.

    Microsoft BizTalk 2010: Line of Business Systems Integration

  8. Here, click Find Task, which brings up the Assembly Qualified Type Name window. Click on the Browse button, select the ScheduledTask.TaskComponents.dll assembly and click open. Now, double click HttpDownload and click Select. Click Ok. Finally, in the Task Properties window, type in the URL http://www.bankofcanada.ca/rss/fx/close/fx-close.xml, leave the user name blank as no authentication is required. Click OK twice and save.

    Microsoft BizTalk 2010: Line of Business Systems Integration

  9. On the Orchestration Properties, we'll assign the logical receive port to the physical port we just created and set both Outbound Logical Ports to the physical send port to connect to Dynamics AX 2009.

  10. After we start our application, it's now time to test it. We can either wait until 5:00 pm, or simply change the Start Time in the Schedule Properties window to a couple minutes past the current time. You'll see a log message in the Windows event log indicating the configuration has changed for the schedule task.
  11. After the scheduled task adapter has fired, and the orchestration has successfully completed, navigate to the Document history form (Basic | Periodic | Application Integration Framework | Document History) in Dynamics AX 2009. Here, you'll see a log for all four messages that were sent and received from our BizTalk application. You can also view the XML message by highlighting one of the Document history log entries, clicking Document logs to bring up the Document log form, and finally clicking View XML.

    (Move the mouse over the image to enlarge.)

  12. Verify that the exchange rate has been created in the table (General Ledger | Setup | Exchange Rates).
  13. Note that the Document history form is company specific and will log all transactions both successful and those that went to error. However, the Exceptions form log is not company specific. Thus, attempting to link an exception to the document history entry can prove to be difficult.

Summary

In this article we used the out-of-the-box AIF services LedgerExchangeRatesService to create a currency exchange rate application that demonstrated the synchronous integration mode of the BizTalk adapter.


Further resources on this subject:


About the Author :


Carl Darski

Carl Darski is a software consultant who has diverse professional experience in sectors including telecommunications, oil & gas, power and mining. As an electrical engineer, Carl quickly recognized the growing importance of software integration technologies. He has assisted several clients in implementing BizTalk as an integration standard.

Books From Packt


Applied Architecture Patterns on the Microsoft Platform
Applied Architecture Patterns on the Microsoft Platform

Microsoft Data Protection Manager 2010
Microsoft Data Protection Manager 2010

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

Microsoft SharePoint 2010 Administration Cookbook
Microsoft SharePoint 2010 Administration Cookbook

Microsoft Dynamics AX 2009 Administration
Microsoft Dynamics AX 2009 Administration

Microsoft Enterprise Library 5.0
Microsoft Enterprise Library 5.0

Microsoft Dynamics Sure Step 2010
Microsoft Dynamics Sure Step 2010

Least Privilege Security for Windows 7, Vista and XP
Least Privilege Security for Windows 7, Vista and XP


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
z
r
m
U
1
H
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software