Variable substitution in appsettings.json Not Working

I have a GitHub Actions workflow where I'm trying to substitute a connection string in the appsettings.json file before deploying to Kubernetes. However, the substitution step doesn't seem to be working, and the value isn't being replaced from the pipeline secrets (set within GitHub Environment secrets).

appsettings.json:

{
  "ConnectionStrings": {
    "DatabaseConnection": "substitute from pipeline"
  }
}

Relevant GitHub Actions workflow:

name: MetricsIngestion-CICD

on:
  push:
    branches: ["development", "master"]
  pull_request:
    branches: ["development", "master"]
  workflow_dispatch:

env:
  IMAGE_NAME: metricsjob
  KUBERNETES_NAMESPACE: wcc-services-cdt

jobs:
  build-and-push-image:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Log in to the Container registry
        uses: docker/login-action@v3
        with:
          registry: ${{ secrets.ACR_ENDPOINT }}
          username: ${{ secrets.ACR_USERNAME }}
          password: ${{ secrets.ACR_PASSWORD }}

      - name: Build and push Docker image
        uses: docker/build-push-action@v5
        with:
          push: true
          file: ABC.Monitoring/Metrics.Ingestion/Dockerfile
          tags: |
            ${{ secrets.ACR_ENDPOINT }}/${{ env.IMAGE_NAME }}:latest 
            ${{ secrets.ACR_ENDPOINT }}/${{ env.IMAGE_NAME }}:${{ github.run_number }}${{ github.run_attempt }}

  Deploy-Preprod:
    runs-on: ubuntu-latest
    needs: [build-and-push-image]

    steps:
      - uses: actions/checkout@v3
  
      # Step for variable substitution in appsettings.json
      - name: Substitute variables in appsettings.json
        uses: microsoft/variable-substitution@v1
        with:
          files: 'ABC.Monitoring/Metrics.Ingestion/appsettings.json'
        env:
          ConnectionStrings.DatabaseConnection: ${{ secrets.DATABASE_CONNECTION }}

      # Deploy to Kubernetes
      - name: Deploy to Kubernetes
        uses: Maersk-Global/custom-github-actions-kubernetes-deploy@v1
        env:
          IMAGE_TAG: ${{ secrets.ACR_ENDPOINT }}/${{ env.IMAGE_NAME }}:${{ github.run_number }}${{ github.run_attempt }}
          CRON_SCHEDULE: ${{ vars.CRON_SCHEDULE_TFDMETRICS }}
          AZURE_AD_CLIENT_ID: ${{ secrets.AZURE_AD_CLIENT_ID }}
          AZURE_AD_TENANT_ID: ${{ secrets.AZURE_AD_TENANT_ID }}
          AZURE_AD_CLIENT_SECRET: ${{ secrets.AZURE_AD_CLIENT_SECRET }}

The Substitute variables in appsettings.json step is not correctly substituting the DatabaseConnection value in the appsettings.json file. The connection string in the file remains "substitute from pipeline", even though I'm passing the environment secret from GitHub Secrets.

I'm using the microsoft/variable-substitution@v1 action for substitution, and the secret DATABASE_CONNECTION is set correctly in the repository as a secret key.

Why is the substitution step failing to replace the DatabaseConnection string, and how can I fix this to correctly replace the connection string from my pipeline secrets?