Most conversations about data migration jump straight to tooling — which enterprise ETL platform, which integration suite, which vendor's connector library. I want to talk about the version that doesn't need any of that: a Windows server that's already sitting in the client's own environment, a SQL Server instance that's already there or trivial to stand up locally, and VB scripting to do the actual work. No license fees, no new vendor relationship, no data leaving the building. Just the fundamentals, applied carefully.
Say the security part first, because it's the part people skip
Before anything else: the data never leaves the client's controlled environment. Not to a laptop, not to a personal OneDrive, not to a "scratch" spreadsheet an analyst keeps open for convenience. An enterprise's production data — customer records, financials, anything with a compliance obligation attached to it — does not get exported to do ETL in a silo somewhere it can't be governed, audited, or deleted on command. That's not a hypothetical risk. It's the single most common way a migration project turns into an incident report.
The whole point of the pattern I'm describing is that it avoids that problem by construction: the script runs on the server, against the server's own SQL instance (or one on the same network, behind the same controls), and the output stays exactly where the input was. Nothing gets copied out to go do the work somewhere more convenient. If a script needs to run somewhere else, the right move is a jump box or a properly provisioned environment inside the client's own boundary — not a workaround that gets data onto a machine nobody's tracking.
The actual pattern: stage it, clean it, don't touch the source until you're sure
The shape of it is almost always the same, whether the source is a twenty-year-old line-of-business database or last year's acquisition that never got integrated:
- Land the source data into staging tables on the target SQL Server — a straight copy, no transformation yet. This is your undo button.
- Write the cleaning logic as real SQL against the staging tables: dedupe, standardize formats, resolve foreign keys, flag anything that doesn't reconcile instead of guessing at it.
- Use VB (classic VBScript for something quick and disposable, VB.NET when it needs to be a real reusable tool) to orchestrate the run — call the SQL, log what happened, handle the parts that are easier to express as procedural logic than as one enormous query.
- Never touch the source system directly. Everything happens in staging until the cleaned result is verified, and only then does it move forward.
The scripts I actually keep and reuse are idempotent — the same "check if it's already been done, then do it" discipline as any migration script that has to be safe to run twice. That single habit is the difference between a script you can hand to someone else and one that only you can safely run, because only you remember which step you're on.
Regenerating load files without ever leaving the server
The other half of this is the output side: most target systems — whether it's a new CRM, a legacy mainframe loader, or a partner's SFTP drop — still want a flat file. CSV, fixed-width, pipe-delimited, whatever their bulk loader expects. Generating that file is a solved problem with nothing more than a SQL query and a VB script writing the result set to disk, run right there on the server where the staged, cleaned data already lives. No export step, no intermediate hop through a machine that isn't part of the controlled environment. The file gets written, it gets picked up by whatever secure transfer process the client already has for exactly this purpose, and the whole exercise never once required data to sit somewhere it shouldn't.
The safest place to clean enterprise data is exactly where it already lives — not somewhere more convenient to work from.
Why this is still worth knowing when so much of it can be bought
I'm not arguing against enterprise integration platforms — for the right scale and the right ongoing need, they earn their cost. But a huge amount of real migration and data-cleanup work is a one-time or occasional project that doesn't justify standing up a new platform for, and doesn't need one. What it needs is someone who can write a correct SQL JOIN, wrap it in a script that's safe to re-run, and do the whole thing inside the client's own walls instead of asking them to trust a new third-party tool with their data. That combination — Server, SQL, and VB — has been available on essentially every Windows environment I've ever worked in, for almost thirty years, and it still is.
It's not a glamorous skill set. Nobody puts "wrote a VBScript to regenerate a load file" on a conference slide. But it's the skill that actually gets a migration done safely, on infrastructure that's already there, without a new vendor, a new contract, or a single record leaving the building it started in.