Exchange rate sync

Updated May 5, 2026

Acme’s finance team bills customers in four currencies. Every invoice needs the day’s exchange rate, and they want it ready before the first invoice goes out each morning. The rate they use comes from an outside rate provider. The provider does not push the rates to Acme, it only answers when you ask. So every morning, early, someone (or something) has to ask the provider for today’s rates and save them into Acme’s own finance database. After that, every pricing lookup reads a local number, fast and with no surprise outside call. If the provider cannot be reached one morning, yesterday’s saved rates stay in place, so finance keeps invoicing on figures that are known to be one day old rather than on nothing at all, and someone is told straight away.

So the job is simple to state. On a fixed morning schedule, fetch today’s rates from the provider, reshape them into Acme’s own format, and save one row per currency into the finance database, ready before invoicing begins. This is a familiar shape, the polling consumer pattern, where work is pulled on a timer instead of waiting to be pushed, here with an outside API as the source rather than a database.

Here is how Art2link ESB builds it. Three parts do the work, and they talk to each other through the bus, the shared message backbone every flow publishes onto and subscribes from. First, a Scheduler receive port (MorningRateClock), a receive port is an entry point that puts a message on the bus, and this one does it on a timer, firing at 05:30. Second, a two-way API Caller send port (RateFetch), a send port is an exit that acts on a message, here calling the provider’s REST endpoint and bringing the answer back. An adapter is the connector that teaches a port how to speak to one kind of system, so the API Caller adapter speaks REST. Third, a one-way SQL Caller send port (RateUpsert) that writes the finance database. A port chooses which messages it acts on with a subscription, a plain rule it matches against messages on the bus.

The 05:30 tick is the trigger. RateFetch subscribes to it, GETs the provider’s daily-rates endpoint, and its inbound flow runs a map, a small transform that rewrites one message shape into another, turning the provider’s JSON response into a canonical ExchangeRates message: your currency pairs, your decimal conventions, the provider’s quirks left at the edge. RateUpsert then subscribes to that message and upserts one row per currency pair into the finance database. End result: by the time invoicing starts, today’s rates are local rows, keyed on pair and date, that any pricing query can read.

EXPRESSION

{{Config.PortName}} == "MorningRateClock"

RateUpsert subscribes on the message type, so re-running a day converges instead of duplicating:

EXPRESSION

{{Message.MessageType}} == "ExchangeRates"

When it fails. Stale rates cost real money quietly, so the alert matters more than the retry. One shared exception type, RateSyncFailed, on both ports, with the standard O365 Mail operations subscription, and because yesterday's rates remain in the table, finance keeps invoicing on known-stale-by-one-day figures knowingly, rather than on a silent failure unknowingly. The held run replays from tracking once the provider is back.

EXPRESSION

{{Message.MessageType}} == "RateSyncFailed"

Date-stamp the rows, keep the history. Upserting on pair + date rather than overwriting a single current-rate row gives finance an audit trail for free, "what rate did we invoice at on the 12th?" is a query, not an investigation.

Build it, step by step. The steps run in dependency order: every object is created before the object that selects it.

Before you start. Create the Application AcmeFinance with its namespace under Applications and select it, so every artifact below lands in it.

Step One

Create the message types

The two types

Under the application’s Message types, create the two types this flow routes on. A message type is a name plus a format, no schema required:

Name	Format	Purpose
ExchangeRates	JSON	the canonical rates document the fetch port publishes (Step 6)
RateSyncFailed	JSON	the shared exception type both send ports publish failures under (Step 8)

No promotions are needed in this flow: nothing downstream binds an individual field.

Step Two

Create the Authentications

Two external parties, two credentials. Under the application’s Authentications, the dialog asks for a Name, the Adapter it pairs with, and a Definition, the credential shape that adapter offers, with the Application preset; the Definition decides the config section that follows.

Rate provider, for the API Caller

Setting	Value
Name	FxRatesApi
Adapter	API Caller
Definition	API Basic Authentication
Username / Password	from the provider’s portal; the adapter presents them on every request

Finance database, for the SQL Caller

Setting	Value
Name	FinanceDb
Adapter	SQL Caller
Definition	SQL Server Connection
Connection String (Database Config)	the finance database’s connection string, credentials included

Step Three

Create the database objects

Create the rates table and the merge procedure in the finance database; the upsert port (Step 7) calls the procedure, and OPENJSON reads the canonical body it passes.

The table and the merge procedure

SQL

CREATE TABLE dbo.ExchangeRates (
    RateDate DATE          NOT NULL,
    Quote    CHAR(3)       NOT NULL,
    Rate     DECIMAL(18,6) NOT NULL,
    CONSTRAINT PK_ExchangeRates PRIMARY KEY (RateDate, Quote)
);

CREATE OR ALTER PROCEDURE dbo.MergeRates
    @RatesJson NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @d DATE = JSON_VALUE(@RatesJson, '$.rateDate');

    MERGE dbo.ExchangeRates AS tgt
    USING (
        SELECT @d AS RateDate, quote, rate
        FROM OPENJSON(@RatesJson, '$.pairs')
        WITH (
            quote CHAR(3)       '$.quote',
            rate  DECIMAL(18,6) '$.rate'
        )
    ) AS src
        ON tgt.RateDate = src.RateDate AND tgt.Quote = src.quote
    WHEN MATCHED THEN UPDATE SET Rate = src.rate
    WHEN NOT MATCHED THEN
        INSERT (RateDate, Quote, Rate)
        VALUES (src.RateDate, src.quote, src.rate);
END;

Step Four

Build the map

Create the map

Under the application’s Maps, create the map the fetch port’s inbound flow (Step 6) will select. The fetch port classifies the provider’s raw response as ExchangeRates on the way in, and this map shapes that payload into the canonical form the type promises, so source and target are the same type:

Map setting	Value
Name	ProviderToExchangeRates
Source message type	ExchangeRates, from Step 1
Target message type	ExchangeRates, from Step 1

The sample provider response

JSON

{
  "date": "2026-06-05",
  "base": "EUR",
  "rates": { "USD": 1.1742, "GBP": 0.8431, "SEK": 11.214 }
}

becomes the canonical document:

JSON

{
  "rateDate": "2026-06-05",
  "base": "EUR",
  "pairs": [
    { "quote": "USD", "rate": 1.1742 },
    { "quote": "GBP", "rate": 0.8431 },
    { "quote": "SEK", "rate": 11.214 }
  ]
}

Author the XSLT

Maps are authored in XSLT 3.0 and run on Saxon HE 12.9. The provider returns rates as an object keyed by currency; map:keys() turns it into one entry per pair:

XSLT 3.0

<xsl:stylesheet version="3.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:map="http://www.w3.org/2005/xpath-functions/map">
  <xsl:output method="text"/>
  <xsl:template match="/">
    <xsl:variable name="in" select="parse-json(.)"/>
    <xsl:variable name="r" select="$in?rates"/>
    <xsl:variable name="pairs" select="
      for $k in map:keys($r)
      return map { 'quote': $k, 'rate': $r($k) }"/>
    <xsl:value-of select="serialize(
      map {
        'rateDate': string($in?date),
        'base': string($in?base),
        'pairs': array { $pairs }
      },
      map { 'method': 'json', 'indent': true() })"/>
  </xsl:template>
</xsl:stylesheet>

Step Five

Create the clock

Create the Scheduler receive port MorningRateClock: Repeat Every 1 / Days, Daily Window opening 05:30:00 AM.

Step Six

Create the fetch port

General

Create a send port RateFetch on the API Caller, subscribing on {{Config.PortName}} == "MorningRateClock", the clock from Step 5. Everything it references already exists, so each field is a pure selection. (The dropdowns can also create types and maps inline; building the dependencies first keeps each dialog a selection.)

Setting	Value
Adapter	API Caller
Way	Two
Authentication	FxRatesApi, from Step 2
Method	GET
URL	https://api.fxrates.example/v1/latest

Classify and map the response

In the port’s inbound flow, classification comes first: the response is typed as ExchangeRates at the start of the inbound flow, then select the ProviderToExchangeRates map from Step 4.

Step Seven

Create the upsert port

General

Create a send port RateUpsert on the SQL Caller, subscribing on {{Message.MessageType}} == "ExchangeRates":

Setting	Value
Adapter	SQL Caller
Way	One
Auth Config	FinanceDb, from Step 2
Command Type	StoredProcedure
Command Text	dbo.MergeRates, from Step 3

Input Parameters

The procedure takes the whole canonical body as one parameter, a key/value row under Input Parameters:

Parameter	Value
RatesJson	{{Message.Body}}

Step Eight

Wire the failure path

Set the Exception Message Type

On both send ports, RateFetch and RateUpsert, set Exception Message Type to RateSyncFailed, from Step 1, so a failed run re-publishes under that type, payload intact.

Create the OpsAlert send port

Create the OpsAlert O365 Mail port subscribing on {{Message.MessageType}} == "RateSyncFailed".

Step Nine

Start the ports and test

Start in dependency order

Everything you configure is live the moment you save it, there is nothing to deploy. Start the send ports first, RateFetch, RateUpsert, and OpsAlert, then the receive port MorningRateClock.

Turn on tracking

Set Tracking to Enabled + Body on every port the flow touches, so you can walk each run step by step, with its message body, in tracking.

Trigger the tick

Trigger a tick; to test without waiting for the 05:30 window, temporarily set MorningRateClock to repeat every 1 Minutes, then restore the schedule. Check the rates table: one row per pair, today’s date. Trigger again and confirm the rows converge (no duplicates).

Break it on purpose

Break the provider URL and confirm operations hears at 05:31, while finance keeps invoicing on yesterday’s rates, knowingly.

Turn bodies off in production. Enabled + Body is the most expensive tracking level, extra processing and database space, and it records every payload including successful runs. Once the flow is proven, set the ports to Only on Error instead: failures still capture the full body for diagnosis, while healthy runs are not recorded.