mcp-mssql-orchestrator

MCP server that orchestrates multi-database NL→SQL flows by fetching from arbitrary sources and staging into MSSQL tables per session.

Staging Target (fixed via .env)

This server uses a single MSSQL instance for staging, configured via environment variables:

STAGE_MSSQL_HOST=localhost
STAGE_MSSQL_PORT=2450
STAGE_MSSQL_USER=sa
STAGE_MSSQL_PASSWORD=YourSecureStagingPassword
STAGE_MSSQL_DATABASE=stage
STAGE_MSSQL_SSL=false

Create a .env file with these values or pass them via Docker envs. The target is not accepted via tool inputs.

See .env.example for all available configuration options including test environment variables.

Tools

• fetch_and_stage: execute SQL on a source DB (mssql/postgres/mysql) and stage results into the fixed MSSQL target table named session_<sessionId>_<suffix> in schema dbo by default.
• execute_sql: execute arbitrary SQL on the fixed MSSQL target and return rows.
• list_staged: list staged tables for a session on the fixed target.
• generate_query: build a SELECT SQL from a JSON spec.
• create_table_from_sql: materialize a SELECT into a new session table on the fixed target.

Typed staging (MSSQL sources)

When the source kind is mssql, fetch_and_stage now preserves native column types:

• The server uses driver metadata from the first result set to derive exact MSSQL types (including precision/scale/length/nullability).
• It creates the destination Stage table with matching SQL types (e.g., DATETIME2, DECIMAL(p,s), NVARCHAR(n), etc.).
• It inserts values using parameterized, typed bindings per column.
• Known user-defined types (UDTs) such as those based on DATETIME are mapped to base types (e.g., TDBDATETIME → DATETIME/DATETIME2).

Benefits:

• Dates remain real dates (filtering/range queries work as expected).
• Numerics remain numerics (aggregations and comparisons are correct).
• Drastically reduces downstream CAST/TRY_CONVERT boilerplate.

Non-MSSQL sources (Postgres/MySQL):

• The server does not have full per-column type metadata across drivers yet; it stages values best-effort and may fall back to text types. You can still materialize typed tables afterward using create_table_from_sql with explicit casts.

How it works (high-level)

1. fetch_and_stage runs your SQL on the source and fetches rows + (for MSSQL) column metadata.
2. If MSSQL source: the Stage table schema is created to match source column types; otherwise it falls back to text for unknown types.
3. Rows are inserted using typed parameters (MSSQL) to preserve fidelity.
4. You can then execute_sql against the session tables or create_table_from_sql to materialize transformations.

Build & Run

cd services/mcp-mssql-orchestrator
npm i
npm run build
node dist/index.js

Dev:

npm run dev

npx usage

Run directly without cloning:

npx mcp-mssql-orchestrator

HTTP transport mode via env:

MCP_TRANSPORT=http MCP_HTTP_PORT=3001 npx -y mcp-mssql-orchestrator

n8n Agent Integration

• Configure your Agent runtime to launch this MCP server via command node dist/index.js (or Docker) and expose the tools.
• Ensure .env is available to the process.

Example Calls

• Stage sales data from an MSSQL source into MSSQL (target comes from .env) with type preservation:

{
  "name": "fetch_and_stage",
  "arguments": {
    "source": { "kind": "mssql", "host": "biopro-sql_local", "port": 1433, "user": "sa", "password": "YourSourceDatabasePassword", "database": "biomain" },
    "sql": "SELECT id, ts, profit_tr FROM dbo.sales WHERE ts >= DATEADD(day, -7, CAST(SYSDATETIME() AS date))",
    "stage": { "sessionId": "abc123", "tableSuffix": "sales_week" }
  }
}

• List staged tables for session:

{
  "name": "list_staged",
  "arguments": {
    "sessionId": "abc123"
  }
}

• Generate a join query across staged tables:

{
  "name": "generate_query",
  "arguments": {
    "spec": {
      "select": ["s.id", "s.ts", "s.profit_tr", "c.usd_rate", "s.profit_tr / c.usd_rate AS profit_usd"],
      "from": "dbo.session_abc123_sales_week s",
      "joins": [{"type": "inner", "table": "dbo.session_abc123_fx_week c", "on": "CAST(s.ts AS DATE) = c.fx_date"}]
    }
  }
}

• Materialize that into a new table and query it:

{
  "name": "create_table_from_sql",
  "arguments": {
    "sessionId": "abc123",
    "tableSuffix": "profits_usd",
    "selectSql": "SELECT s.id, s.ts, s.profit_tr, s.profit_tr / c.usd_rate AS profit_usd FROM dbo.session_abc123_sales_week s INNER JOIN dbo.session_abc123_fx_week c ON CAST(s.ts AS DATE) = c.fx_date"
  }
}

Docker

FROM node:20-alpine
WORKDIR /app
COPY package.json package-lock.json ./
RUN npm i --omit=dev
COPY src ./src
COPY tsconfig.json ./
RUN npm run build
ENV STAGE_MSSQL_HOST=localhost \
    STAGE_MSSQL_PORT=2450 \
    STAGE_MSSQL_USER=sa \
    STAGE_MSSQL_PASSWORD=YourSecureStagingPassword \
    STAGE_MSSQL_DATABASE=stage \
    STAGE_MSSQL_SSL=false
CMD ["node", "dist/index.js"]

To override, pass --env-file .env or -e flags.

MSSQL Staging Container (port 2450)

Run SQL Server locally on 2450 and create database stage:

# Start MSSQL
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourSecureStagingPassword' \
  -p 2450:1433 --name mssql-2450 -d mcr.microsoft.com/mssql/server:2022-latest

# Create the stage database once
docker exec -it mssql-2450 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'YourSecureStagingPassword' -Q "IF DB_ID('stage') IS NULL CREATE DATABASE stage;"

Ensure your .env matches these values (or adjust accordingly).

Notes

• For MSSQL sources, staging preserves base types using driver metadata and typed inserts. UDTs are mapped to their base types for storage (e.g., TDBDATETIME → DATETIME2).
• For Postgres/MySQL sources, columns may stage as text when exact typing is unavailable; you can materialize typed tables via create_table_from_sql with explicit casts.
• Table names are sanitized. Session isolation is achieved via session_<sessionId>_* naming.