How We Rebuilt Our SSRS to PostgreSQL Converter

Two weeks ago, the automated T-SQL to PostgreSQL converter behind ReportBridge was failing most of the queries in a production report library. Only a small minority of reports compiled and ran against the target PostgreSQL; the rest failed somewhere between the prompt and the execution plan.

Today most of them convert and run. The reports still failing are a mix of structural problems (recursive CTEs, cross-database references) that no amount of better prompting will solve on its own.

This is what it took to get there. None of the changes were individually heroic — but the order mattered, and the shape of the fallback matters even more.

Step 1: Diagnosing a low pass rate

When an AI-powered converter fails most of what you give it, the first instinct is to blame the model. It's usually wrong. Three instrumentation changes told us the real story:

The model wasn't hallucinating. It was faithfully translating against a schema that no longer existed. That turned out to be the largest single cause of the failures.

Step 2: Fixing the schema cache

The converter had been pulling schema from a cached snapshot that only refreshed on manual trigger. Three migrations on the source side — a column rename, a table split into two tables, and a new view replacing a deprecated one — had never been reflected. Every report that touched those objects failed at conversion time.

// Before: cached once, never invalidated
const schema = await readCachedSchema(targetDbId);

// After: TTL-bounded cache with explicit invalidation hooks
const schema = await getSchema(targetDbId, {
  maxAgeMs: 30 * 60 * 1000,
  onMigrationEvent: invalidateSchemaCache,
});

The fix was small: a 30-minute TTL plus an invalidation hook fired by the migration runner. Once the next conversion run picked up the fresh schema, the pass rate made the biggest jump of the whole effort, in a single deploy.

Step 3: Reserved-word collisions

PostgreSQL reserves a different set of keywords than SQL Server, and most T-SQL code was written against a looser list. Columns named user, order, type, desc, or grant are perfectly legal in SQL Server and fail instantly in PostgreSQL unless quoted.

SELECT user, type, desc
FROM records
WHERE grant = 'active'

-- ERROR: syntax error at or near "user"
SELECT user, type, desc
FROM records
WHERE grant = 'active'

-- Needs:
SELECT "user", "type", "desc"
FROM records
WHERE "grant" = 'active'

We added a reserved-word scrub pass that runs after the model output: any identifier that collides with a PostgreSQL reserved word gets double-quoted. The scrub is deterministic, which matters — asking the model to do it probabilistically introduced occasional errors when the reserved word appeared inside string literals or aliases. Doing it in code cleared a whole class of previously-failing reports.

Step 4: Subquery aliases and the silent bug

The worst class of failure isn't the one that throws an error. It's the one that compiles, runs, and returns the wrong answer. This is the "stripped alias" bug:

SELECT e.name,
  LastReview = (SELECT TOP 1 r.review_date
                FROM reviews r
                WHERE r.emp_id = e.id
                ORDER BY r.review_date DESC)
FROM employees e

SELECT e.name,
  (SELECT r.review_date
   FROM reviews r
   WHERE r.emp_id = e.id
   ORDER BY r.review_date DESC) AS last_review
FROM employees e

-- Missing LIMIT 1 — subquery returns multiple rows
-- PostgreSQL will throw at runtime: "more than one row returned"
-- OR silently pick an arbitrary row depending on driver

The TOP 1 was stripped during the T-SQL-to-ANSI normalization step, but the compensating LIMIT 1wasn't added. We caught this during validation only because the pipeline now runs every converted query through a row-count sanity check. Without that, a perfectly-compiling report could ship with subtly-wrong numbers. This is the bug that convinced us validation matters more than conversion.

Step 5: The correction tools that close the gap

A pass rate is not the goal of an automated conversion pipeline. The goal is a shipped report library. To close the gap between "automated" and "shipped" we built five manual-correction tools that live inside the same admin UI:

The economics are the point. When most queries fail automated conversion, a migration team has to manually rewrite most of the library — that's weeks of work. When most of them pass and good correction tools cover the rest, the team hand-finishes only the remainder. That's the difference between a migration that ships and one that stalls.

What I'd tell myself at the start

ReportBridge's converter ships these five fixes by default. If you're evaluating tools for an SSRS migration of your own and your current approach is failing most of your queries the way ours was at the start, I'd love to hear about the failure categories you're seeing — they're often domain-specific and the taxonomy benefits everyone.

FAQ