What this covers
- Import and export routines between systems with no native integration
- T-SQL and SSIS on Microsoft SQL Server, readable by whoever inherits it
- One-off conversions with reconciliation built into the process
- Fixed-width, delimited and legacy flat-file layouts
- Recurring loads scheduled, logged and alerting on failure
- Reporting extracts that stop being rebuilt by hand each month
- Written to be readable and maintainable by your team, not just by me
Most systems do not integrate. They exchange files.
Vendors talk about integration; what most organisations actually have is one system producing a file and another consuming it, with a person in the middle doing something in Excel. That person is a single point of failure, the process is undocumented, and mistakes are invisible until someone downstream notices a number is wrong.
This is unglamorous work and it is where a surprising amount of operational risk lives.
What this covers
Import and export routines. Getting data from one system into another when there is no supported integration: reading the source’s export, transforming it to the target’s expected layout, loading it, and reporting what did not load and why. Rejected records should be a report someone reads, not a silent skip.
One-off conversions. Moving data between platforms as part of a system replacement, with the mapping written down and the results reconciled against the source rather than eyeballed.
Legacy file layouts. Fixed-width extracts with packed fields, dates in five different formats, and record types distinguished by a character in column 3. Older systems produce exactly this, and it is entirely workable once someone sits down with the layout document.
Recurring loads. The nightly or monthly job that currently depends on somebody remembering — scheduled, logged, and alerting when it fails, so a failure is noticed the same day.
Reporting extracts. The pull that gets rebuilt by hand every month because the original was written once and never documented.
How it is written
Mostly Microsoft SQL Server and T-SQL, because that is what is already running at most organizations that need this work and what their staff can read. Stored procedures and views over clever abstractions, SQL Server Agent for scheduling, and SSIS where a package genuinely fits the job better than a procedure.
Parameters instead of hard-coded values. Logging instead of silent failure. A dry-run mode for anything that writes. Comments aimed at whoever inherits it. The code goes into your repository or your server, documented, with a walkthrough session — the measure of success is that your team modifies it confidently after I am gone.
Reconciliation is part of the job
Any process that moves data produces a count of what was read, written and rejected, and those numbers get compared. A load that quietly drops five percent of its rows is worse than one that fails outright, because nobody finds out for months.
Common questions
Our data lives in a system with no API. Can it still be automated?
Almost always. If the system can produce a scheduled export, or its database can be read directly, that is enough to build a reliable exchange around. An API is convenient, not a prerequisite.
Who owns the code afterwards?
You do, with no licensing strings. It goes into your environment, documented, with a handover session for whoever will maintain it.
Can you work against production?
Read-only, yes, with agreed windows. Anything that writes gets developed and proven against a copy first, and ships with a dry-run mode.