A small YARP reverse proxy that makes JSON REST APIs consumable by the SSRS / Power BI Report Server XML data source. It forwards GET requests to a configured upstream and rewrites the JSON response as a flat, tabular XML document. Auth (API keys, bearer tokens, etc.) is configured declaratively using YARP's built-in transforms — no custom code.

Deployment model: this proxy is intended to run locally on the report server (the SSRS / PBIRS box) and listen on localhost only. SSRS data sources point at http://localhost:5080/.... The proxy injects API keys from its own config so the keys never appear in report definitions or shared data source connection strings. Do not expose it on a routable interface — it has no authentication of its own. By default it binds only to the loopback interface (127.0.0.1 and ::1).

SSRS and Power BI Report Server ship an "XML" data source that can hit any URL and bind the response as a dataset — but it expects XML, not JSON, and its query language is finicky about element shapes and types. Most modern REST APIs return JSON. This proxy sits between them and emits a single predictable XML shape (<Root><Row>...</Row>...</Root>) so that:

• No SSRS dataset query is required (auto-detect finds <Row>).
• The same one-line query works for every endpoint.
• A ?_query=1 debug URL returns a paste-ready <Query> document with inferred field types (Integer, Decimal, Boolean, DateTime).

1. A YARP route maps an incoming proxy path to an upstream cluster (appsettings.json → ReverseProxy:Routes / Clusters).
2. Auth and any other request shaping uses YARP's built-in transforms (RequestHeader, QueryValueParameter, PathPattern, …).
3. A response transform buffers the upstream JSON and emits a tabular XML document — always the same shape, so the SSRS dataset needs no query at all (auto-detect picks <Row>), or one boilerplate query that works for every endpoint:

   <Query><ElementPath IgnoreNamespaces="true">Root/Row</ElementPath></Query>

4. Appending ?_query=1 to any proxied URL returns a generated <Query> document with type-inferred fields, ready to paste into Report Builder.

<Root>
  <Row>...scalar fields...</Row>
  <Row>...</Row>
</Root>

1. If the route has Metadata.RowPath configured, that dotted JSON path is resolved; if it's an array, its items are the rows.
2. Else if the top-level JSON value is an array, its items are the rows.
3. Else if the top-level value is an object with exactly one record-like array property (array of objects), that array's items are the rows.
4. Else the whole top-level object is a single row.
5. A scalar top-level value becomes one row with a <Value> field.

• Nested objects collapse with _: address.geo.lat → <address_geo_lat>.
• Nested arrays are serialized as a compact JSON string in one element (so the row stays tabular and the column set is fixed per endpoint).
• null → empty element. Numbers/booleans → text form.
• XML-illegal characters in JSON property names are sanitized (@context → _context, leading xml* is prefixed with _).

Add a route + cluster under ReverseProxy. Authentication is just another transform — no custom config section needed.

"ReverseProxy": {
  "Routes": {
    "myapi": {
      "ClusterId": "myapi",
      "Match": { "Path": "/myapi/{**catch-all}" },
      "Metadata": { "RowPath": "data.items" },   // optional, only if rows are nested
      "Transforms": [
        { "PathPattern": "/{**catch-all}" },
        { "RequestHeader": "X-API-Key", "Set": "REPLACE-ME" }
      ]
    }
  },
  "Clusters": {
    "myapi": {
      "Destinations": {
        "d1": { "Address": "https://api.example.com/" }
      }
    }
  }
}

Layered .NET config means env vars or User Secrets override the file. The Transforms array is positional; the key is the transform's index:

# Override the "Set" value of the 2nd transform on route "myapi"
$env:ReverseProxy__Routes__myapi__Transforms__1__Set = "real-secret-key"

Or in dev:

dotnet user-secrets set "ReverseProxy:Routes:myapi:Transforms:1:Set" "real-secret-key"

Run the proxy and try:

Append ?_query=1 to any of these to get a paste-ready <Query> document.

dotnet run

Listens on http://localhost:5080 by default (loopback only). Override with --urls or the ASPNETCORE_URLS environment variable if you really must bind elsewhere — but see the warning at the top.

The app uses Microsoft.Extensions.Hosting.WindowsServices, so it can be launched directly by the Service Control Manager.

From an elevated PowerShell prompt in the cloned repo:

.\install.ps1

That will:

1. dotnet publish a self-contained win-x64 build into C:\Program Files\XmlDataSourceProxy (override with -InstallDir).
2. Create a service named XmlDataSourceProxy running under the NT SERVICE\XmlDataSourceProxy virtual account (auto-created by SCM, no password to manage).
3. Grant that account read/execute on the install directory.
4. Configure auto-start and a restart-on-failure policy.
5. Start the service and verify it's listening on 127.0.0.1:5080.

Re-running install.ps1 performs an in-place upgrade (stop, overwrite, restart).

To remove:

.\uninstall.ps1            # stop + delete service, leave files
.\uninstall.ps1 -RemoveFiles   # also delete C:\Program Files\XmlDataSourceProxy

dotnet publish -c Release -r win-x64 --self-contained `
    -o "C:\Program Files\XmlDataSourceProxy"

sc.exe create XmlDataSourceProxy `
    binPath= "\"C:\Program Files\XmlDataSourceProxy\XmlDataSourceProxy.exe\"" `
    start= auto `
    obj= "NT SERVICE\XmlDataSourceProxy" `
    DisplayName= "XML Data Source Proxy for SSRS/PBIRS"

sc.exe start XmlDataSourceProxy

Logs go to the Windows Application event log under source XmlDataSourceProxy by default. For richer/structured logs, drop a Serilog/Seq sink into Program.cs — not included to keep the project minimal.

The published folder (C:\Program Files\XmlDataSourceProxy) contains the default appsettings.json from this repo. Either edit it in place or override individual settings with environment variables (preferred for secrets — env vars don't get checked into source control, and the virtual service account can read them without anyone seeing them in JSON):

# Per-service environment variable (PowerShell, elevated).
New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\XmlDataSourceProxy" `
    -Name Environment -PropertyType MultiString `
    -Value @("ReverseProxy__Routes__myapi__Transforms__1__Set=real-secret-key")
Restart-Service XmlDataSourceProxy

1. Data source type: XML. Connection string: the proxy URL.
2. Create a dataset. Either leave the query empty (auto-detect finds <Row>) or paste the boilerplate <Query><ElementPath IgnoreNamespaces="true">Root/Row</ElementPath></Query>.
3. For real types instead of String, hit the URL with ?_query=1, copy the returned <Query>...</Query> block, and paste that into the dataset Query box. It includes inferred Integer / Decimal / Boolean / DateTime annotations.
4. Refresh Fields, drag, render.

Program.cs                       -- wires YARP + middleware
DesignTimeQueryMiddleware.cs     -- snoops/strips ?_query=1
JsonToXmlTransformProvider.cs    -- registers the response transform per route
JsonToXmlResponseTransform.cs    -- JSON -> tabular XML
QueryDocumentBuilder.cs          -- builds the <Query> document
appsettings.json                 -- routes, clusters, transforms