What this covers
- A Microsoft SQL Server reporting database built from systems that were never designed to share
- Fixed-width, delimited, XML, JSON, spreadsheet and direct database sources
- T-SQL and SSIS pipelines scheduled with SQL Server Agent, not rebuilt monthly by hand
- Reconciliation against the source systems, so the numbers are defensible
- Failures that announce themselves rather than producing a stale report
- Documented and handed over, so your team can add the next source
The reports are fine. It’s the questions that cross systems that aren’t.
Every system produces its own reports, and each one is accurate about its own slice of the world. The trouble starts with any question that spans two of them — and those are usually the questions worth asking. Answering them turns into somebody exporting to Excel, pasting, matching things up by hand, and producing a number nobody can reproduce next month.
I’ve done this work repeatedly across my career: taking data from many different places, in whatever format each system was willing to give up, and combining it into one place that answers those questions — kept current automatically rather than rebuilt by hand every reporting cycle.
What this actually involves
Finding out what each source will give you. Some systems have a supported export. Some have a database you can read directly. Some will only produce a report on a schedule to a folder. Each has its own quirks: fixed-width layouts with packed fields, dates in three different formats, record types distinguished by a character in a fixed column, encodings that predate Unicode. The awkward ones are usually the ones holding the data you most want.
Deciding what “the same thing” means. This is the hard part, and it is a business question rather than a technical one. If two systems both have a customer record, what makes them the same customer? If two systems both report a balance as of month end, do they mean the same moment? Getting this wrong produces a database that looks right and is quietly useless.
Building the pipeline. Extract, transform and load, with each stage logged and individually re-runnable. Loads land in a staging area first, so a bad source file is caught before it reaches anything anyone reports from. Reruns are safe and don’t duplicate.
Keeping history. A reporting database that only holds current state can’t answer questions about trend, which is most of what anyone actually wants. Deciding what to snapshot and how far back to keep it is a design decision worth making deliberately.
Making it self-announcing. A pipeline that fails silently is worse than no pipeline, because people keep trusting the numbers. Every load reports success or failure somewhere a human will see it, and a stale table should be obvious rather than invisible.
Built on SQL Server, mostly
Most of this work lands in Microsoft SQL Server, and most of it is T-SQL. That is a deliberate preference as much as a habit: the platform is already in the building at almost every organisation that needs this, the staff who will inherit it can usually read T-SQL, and SQL Server Agent handles scheduling without adding another tool to maintain.
In practice that means a staging schema that raw loads land in untouched, transformation in stored procedures that can be read and re-run, views presenting a stable shape to whatever reports against it, and Agent jobs with failure notifications. SSIS where a package genuinely suits the job better than a procedure — usually for messy file sources — rather than by default.
Where the source is something else entirely, that’s a connection problem rather than a change of approach: linked servers, an ODBC connection, a scheduled export, or a small PowerShell or Python step to fetch and normalise before the load runs.
Where this comes up
- A board or management report assembled by hand each month from several systems
- Regulatory or compliance reporting that draws on more than one platform
- Analytics or a dashboard tool that needs one consistent source rather than five
- An acquisition leaving two of everything, with reporting expected across both
- The spreadsheet that has quietly become critical infrastructure and only one person understands
- Retiring a legacy system, but needing its history to remain queryable
What you get
A reporting database with a documented schema, the load pipelines and the scheduling around them, reconciliation reports, and a written description of each source and how it maps in. Written so your team can add the seventh source without me.
Common questions
How is this different from your database programming service?
Database programming is point to point — getting data out of system A and into system B correctly. Aggregation is many to one, and ongoing: a dozen sources landing in a single reporting database on a schedule, with history retained so you can compare periods. Projects often start as the former and grow into the latter.
Do we need a data warehouse product for this?
Usually not. Most organisations asking this question need a well designed reporting database on the platform they already run, loaded on a schedule. Warehouse and lakehouse products solve problems of scale that most mid-sized organisations do not have, and they add licensing and skills you would then need to keep.
Our source systems don't have APIs.
That is the normal case, not the exception. A scheduled export to a file share, or read-only access to the underlying database, is enough to build a reliable pipeline. Much of this work involves formats that predate the idea of an API.
How do we know the aggregated numbers are right?
Reconciliation is part of the build, not an afterthought. Each load records what it read, wrote and rejected, and those counts are compared back to the source. A report that quietly drops five percent of its rows is worse than one that fails outright.