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:

Promotions live on the message type, so add the one this flow binds while you are here. The SFTP filename (Step 8) binds it, and adapter parameters are plain strings: they bind {{…}} tokens but never evaluate a body path.

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.

Create the stock table and the extract procedure in the ERP database; the extract port (Step 7) calls the procedure. The snapshot date rides on every row so the filename template and the 3PL file agree on it. The SELECT ends in FOR JSON PATH, so the multiple rows come back as a JSON array, the shape the inbound map (Step 4) iterates.

CREATE TABLE dbo.StockOnHand (
    Sku      VARCHAR(40) NOT NULL PRIMARY KEY,
    OnHand   INT NOT NULL,
    Reserved INT NOT NULL CONSTRAINT DF_Stock_Res DEFAULT 0
);

CREATE OR ALTER PROCEDURE dbo.GetStockSnapshot
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        CONVERT(CHAR(10), SYSUTCDATETIME(), 23) AS SnapshotDate,
        Sku, OnHand, Reserved
    FROM dbo.StockOnHand
    ORDER BY Sku
    FOR JSON PATH;
END;

Under the application’s Maps, create the map the extract port’s inbound flow (Step 7) will select:

Because the procedure’s SELECT ends in FOR JSON PATH, the rows come back as a JSON array, one object per SKU:

[
  { "SnapshotDate": "2026-06-04", "Sku": "TP-4501", "OnHand": 118, "Reserved": 12 },
  { "SnapshotDate": "2026-06-04", "Sku": "HX-0090", "OnHand": 64,  "Reserved": 0 }
]

The map collapses that array into one canonical document, lifting the snapshot date off the first element:

<Stock>
  <SnapshotDate>2026-06-04</SnapshotDate>
  <Item><Sku>TP-4501</Sku><OnHand>118</OnHand><Reserved>12</Reserved></Item>
  <Item><Sku>HX-0090</Sku><OnHand>64</OnHand><Reserved>0</Reserved></Item>
</Stock>

Maps are authored in XSLT 3.0 and run on Saxon HE 12.9. JSON in, XML out: parse-json(.) turns the result into an array, ?1?SnapshotDate lifts the date off the first element, and ?* iterates the array:

<xsl:stylesheet version="3.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml" indent="yes"/>
  <xsl:template match="/">
    <xsl:variable name="rows" select="parse-json(.)"/>
    <Stock>
      <SnapshotDate><xsl:value-of select="$rows?1?SnapshotDate"/></SnapshotDate>
      <xsl:for-each select="$rows?*">
        <Item>
          <Sku><xsl:value-of select="?Sku"/></Sku>
          <OnHand><xsl:value-of select="?OnHand"/></OnHand>
          <Reserved><xsl:value-of select="?Reserved"/></Reserved>
        </Item>
      </xsl:for-each>
    </Stock>
  </xsl:template>
</xsl:stylesheet>

The second map runs on the upload port’s outbound side (Step 8) and produces the file the 3PL ingests:

Semicolon-delimited, date compacted to yyyymmdd, quantity available computed per line:

SKU;QTY_AVAILABLE;SNAPSHOT_DATE
TP-4501;106;20260604
HX-0090;64;20260604

One template over the canonical document:

<xsl:stylesheet version="3.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text"/>
  <xsl:template match="/Stock">
    <xsl:variable name="d" select="translate(SnapshotDate, '-', '')"/>
    <xsl:text>SKU;QTY_AVAILABLE;SNAPSHOT_DATE&#10;</xsl:text>
    <xsl:for-each select="Item">
      <xsl:value-of select="Sku"/>
      <xsl:text>;</xsl:text>
      <xsl:value-of select="number(OnHand) - number(Reserved)"/>
      <xsl:text>;</xsl:text>
      <xsl:value-of select="$d"/>
      <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Create a receive port NightlyStockClock on the Scheduler: Repeat Every 1 / Repeat Unit Days, Daily Window opening 11:00:00 PM.

Create a send port StockExtract on the SQL Caller, subscribing on {{Config.PortName}} == "NightlyStockClock", the clock from Step 6. 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.)

In the port’s inbound flow, select ResultToStockSnapshot, from Step 4, so the extract is published as StockSnapshot.

Create a send port StockToWarehouse on the SFTP Caller, subscribing on {{Message.MessageType}} == "StockSnapshot". The Filename binds the SnapshotDate promotion from Step 1:

On the outbound side, select the StockSnapshotTo3plCsv map from Step 5 (Typed, keyed on StockSnapshot).

On both send ports, StockExtract and StockToWarehouse, set Exception Message Type to StockExportFailed, from Step 1, and give the SFTP port generous retries first.

Create an O365 Mail port OpsAlert subscribing on {{Message.MessageType}} == "StockExportFailed".

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

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.

To get a tick without waiting for 23:00, temporarily set NightlyStockClock to Repeat Every 1 / Repeat Unit Minutes with no Daily Window, let it fire once, then restore the nightly schedule. Confirm the dated file lands complete on the 3PL server (the atomic write means no partial file is ever visible), and the run reads cleanly in tracking. Re-run the same night and confirm the file overwrites its own date.

Block the upload and confirm the alert plus replay.