File-Based Integration

If you have worked in enterprise integration for any length of time, you have met file-based integration, whether or not anyone called it that. A payroll system drops a batch of transactions into a folder every night. An ERP exports a supplier master as a comma-separated file for a warehouse system to pick up. A bank sends a fixed-width settlement file over SFTP at the close of business. None of this is glamorous, and yet a large share of the data moving between serious systems today still moves as files. This guide sits under the broader map of integration styles in my enterprise system integration pillar, and it zooms in on the pattern that refuses to die because, on the right problem, nothing beats it for robustness and simplicity.

The message up front: file-based integration is not a legacy embarrassment to be replaced at the first opportunity. It is a deliberate architectural choice that trades real-time immediacy for extreme decoupling, universal compatibility and operational simplicity. On batch-shaped, high-volume, tolerant-of-latency problems it is often the correct answer, not the fallback. Knowing when to reach for it, and how to run it well, is a core integration skill.

1. What file-based integration is

File-based integration is an integration pattern in which one system communicates with another by producing a file, and the second system consumes that file. The source system exports its data into a structured document, places it somewhere the target can reach, and the target reads it on its own schedule and loads it into its own database. There is no live connection between the two systems, no shared API, no synchronous request and response. The file is the interface, and the file is the contract.

That last point is the one people underestimate. In a file-based integration, the format and layout of the file is the agreement between the two systems. The producer promises to write columns in a certain order, dates in a certain format, and records terminated a certain way. The consumer promises to read exactly that. As long as both sides honour the file specification, neither needs to know anything about how the other is built, what language it runs, or even whether it is online at the moment the file is produced. This is a genuinely different mental model from an API call, where both systems must be available at the same instant and must speak a live protocol.

It helps to name the three moving parts, because the rest of this guide keeps returning to them. There is the export, where the source system serialises data into a file. There is the transfer, where the file moves from where it was written to where it will be read, whether that is a shared network folder, an SFTP server or a cloud storage bucket. And there is the import, where the target system picks the file up, parses it, validates it and loads it. Get all three right and file-based integration is boringly reliable, which in production is the highest compliment an integration can earn.

2. Why it endures (the oldest pattern)

File-based integration predates the web, predates web services, and predates most of the middleware that was supposed to replace it. Systems have been exchanging files on tapes, disks and network shares since mainframes ruled the data centre, and the pattern has survived every wave of newer technology for a simple reason: it keeps working when other things break. The endurance is not nostalgia. It reflects a set of properties that remain genuinely valuable.

The first is that almost every system on earth can read and write a file. A forty-year-old mainframe application and a brand-new cloud service have very little in common, but both can produce a flat file, and that shared lowest common denominator makes files the universal solvent of integration. When you need to connect two systems that share no modern protocol, a file is very often the only interface they both already support.

The second is that files decouple systems in time. An API integration requires both systems to be up and responsive at the same moment. A file integration does not. The producer can write its file at two in the morning while the consumer is offline for maintenance, and the consumer can pick it up hours later when it comes back. Neither system's availability depends on the other's. In environments with unreliable networks, scheduled downtime or systems in different time zones, that temporal decoupling is not a minor convenience, it is the thing that makes the integration possible at all.

The third is that files match how a great deal of business data is actually generated and consumed: in batches. Payroll runs once a fortnight. Billing closes once a month. A day's transactions settle at end of day. When the natural rhythm of the data is periodic rather than continuous, a file that captures a whole batch at once is a more honest representation of the work than a stream of individual API calls pretending the data arrived one record at a time. For the broader trade-off between periodic and continuous movement, see my batch versus real-time integration guide.

3. How it works: export, transfer, import

The canonical file-based integration is a three-stage relay. System A exports a file to a shared location or an SFTP server. System B polls that location on a schedule, discovers a new file, and imports it. The diagram below walks the whole lifecycle, from the source database to the target database, including the two details that separate a reliable implementation from a fragile one: the control file that signals completeness, and the archive step that gives you an audit trail.

Read the flow left to right. In stage one, System A exports the day's data and writes it to the shared location as a data file. Crucially, once the data file is complete, it also writes a small control file, shown here with a .done extension. This second file is the signal that the first one is finished and safe to read. In stage two, System B polls the location on a schedule, say every fifteen minutes, and looks specifically for the control file. Only when the control file appears does it, in stage three, open the data file, parse it, validate the records and load them into its own database. In stage four it archives the processed file so there is a record of what was received and when.

The single most common bug in naive file-based integrations lurks between stages one and two: the consumer picks up the data file while the producer is still writing it, and imports a truncated batch. The control-file pattern in the diagram exists precisely to prevent that race. The producer writes the whole data file first and only then writes the tiny control file, so the appearance of the control file is an atomic all-clear. It is a small discipline that turns a flaky integration into a dependable one.

4. Common formats and transports

A file-based integration is defined by two choices: the format of the file, which is how the data is structured inside it, and the transport, which is how the file moves from producer to consumer. These are independent decisions. You can send a CSV over SFTP, a JSON document to a cloud bucket, or a fixed-width file across a shared folder. The table below lays out the formats and transports you will meet most often, with the situations each one tends to suit.

A few practitioner notes on this menu. CSV is the workhorse and the most misunderstood format on the list, because the moment your data contains commas, quotes or line breaks inside a field, the naive version breaks and you need a proper parser that honours quoting rules. XML buys you schema validation and rich structure at the cost of verbosity, which is why it dominates regulated industries where the schema is the point. JSON has quietly taken over new-build exchange because it is compact and maps cleanly onto modern data structures. Fixed-width feels archaic until you have to interface with a bank or a mainframe, at which point it is often the only format on offer, and its rigidity becomes a virtue because there is no delimiter to escape. On transports, SFTP is the default whenever the file crosses an organisational boundary, and it deserves its own treatment in my SFTP integration guide; the CSV format likewise gets a dedicated deep-dive in the CSV integration guide.

5. Strengths: simple, universal, decoupled

The case for file-based integration rests on three strengths that compound each other. The first is simplicity. There is very little to understand: write a file, move a file, read a file. There is no message broker to operate, no API gateway to secure, no service to keep online. When something goes wrong you can open the file and look at it with your own eyes, which is a debugging superpower that stream-based and API integrations simply do not offer. The whole pattern is inspectable, and inspectability is worth an enormous amount when a production feed misbehaves at three in the morning.

The second strength is universality. Because every platform can read and write files, file-based integration connects systems that share nothing else. Old and new, on-premise and cloud, one vendor and another, all of them can meet on a flat file. This makes the pattern the reliable escape hatch when a modern API integration is impossible because one side does not have an API worth using. I have connected systems that had no business talking to each other, and the file was the only common ground.

The third strength is decoupling. The producer and consumer are independent in time, in technology and in failure. If the consumer is down, files pile up harmlessly and are processed when it recovers. If the producer changes its internal database, the consumer neither knows nor cares as long as the file format holds. This loose coupling is the property that keeps file-based integrations running for a decade with minimal maintenance, long after tighter integrations built around a specific API have been forced to change three times because the API did.

The honest limitation: every strength above is also the source of a weakness. The same decoupling that makes files robust also makes them slow, because nothing happens until the next scheduled run. The same simplicity that makes files easy to inspect also means the pattern gives you almost nothing for free: no built-in acknowledgement, no automatic retry, no transaction. Whatever error handling and delivery guarantees you need, you have to build yourself around the file.

6. Weaknesses: latency, error handling, no real-time

The first and most obvious weakness is latency. File-based integration is inherently batch. Data becomes available to the consumer no sooner than the next time the file is produced and the next time the consumer polls. If the producer writes nightly and the consumer runs every hour, a change made at nine in the morning is not reflected downstream until the following day. For a payroll or billing feed that cadence is fine. For anything where a user expects a change to appear promptly, it is unacceptable. File-based integration is structurally unable to be real-time, and no amount of tuning changes that; it only shortens the batch interval.

The second weakness is error handling. A file is an all-or-nothing lump of data, and when record four thousand in a fifty-thousand-record file is malformed, you face a design question the pattern does not answer for you: do you reject the whole file, skip the bad record and continue, or halt and wait for a human? Each choice has consequences, and the pattern gives you no default. Worse, because the two systems never speak directly, the producer often has no idea the consumer choked on its file. Without a deliberately built feedback channel, errors are silent, and silent errors in a nightly feed can run undetected for days.

The third weakness is the absence of any real-time or interactive capability. There is no request and response, no way for the consumer to ask the producer a question, no way to confirm receipt within the same operation. If your integration needs a system to react to an event the instant it happens, to validate something live, or to return an answer to a waiting caller, files are the wrong tool and no clever engineering will make them right. That is the boundary where you move to an API or an event stream, and recognising the boundary honestly is more useful than trying to stretch files past it.

There is a quieter fourth weakness worth naming: duplicate and ordering hazards. If a file is delivered twice, or a run is re-triggered after a partial failure, a poorly designed consumer will load the same records again. If files can arrive out of order, a consumer that assumes strict sequence will corrupt its own state. These are not reasons to avoid file-based integration; they are reasons to design the import defensively, which is exactly what the best practices below address.

7. Best practices: naming, control files, idempotency, checksums

Everything that separates a file-based integration you can trust from one that pages you every week comes down to a handful of disciplines. None of them is difficult, and all of them are routinely skipped by teams treating file transfer as too simple to need engineering.

• File naming conventions: name files so that both humans and machines can reason about them. A pattern like payroll_20260709_0200.csv encodes the feed, the date and the run time, which makes ordering obvious, makes duplicates visible, and makes the archive self-documenting. Never overwrite yesterday's file with today's under a static name; you destroy your own history and invite races.
• Control files: as in the diagram, write the data file first and a small separate control file second. The consumer waits for the control file before touching the data. This one convention eliminates the single most common failure in the whole pattern, the partial-read race, and it costs almost nothing to implement.
• Idempotency: design the import so that processing the same file twice produces the same result as processing it once. Key records on a stable business identifier and use an insert-or-update rather than a blind insert, so a re-delivered or re-run file cannot create duplicates. Idempotency is what lets you retry safely, and safe retry is what lets you sleep.
• Checksums and record counts: include a checksum or a record count, often inside the control file, so the consumer can verify it received the complete, uncorrupted file before loading it. A file that arrives with nineteen thousand rows when the control file says twenty thousand should be quarantined, not loaded. This catches truncated transfers and corruption before they contaminate the target.
• Archiving and audit: move every processed file to a dated archive rather than deleting it. When someone asks in three weeks why a number is wrong, the archived files are the ground truth that lets you reconstruct exactly what was received and when.
• Feedback and alerting: build the acknowledgement the pattern does not give you. At minimum, alert a human when an expected file does not arrive by its deadline, and when a file fails validation. A missing nightly file is itself an event, and silence should never be mistaken for success.

Notice that none of these require exotic tooling. They are conventions and small pieces of glue code, and together they convert file-based integration from a pattern that looks fragile into one that is genuinely dependable at scale. The teams that get burned by files are almost always the teams that treated the transfer as trivial and skipped these disciplines.

8. When to use file-based integration

File-based integration is the right tool when the shape of the problem matches its strengths. Reach for it when the data is naturally batch, produced or consumed on a periodic cadence rather than continuously. Reach for it when latency of minutes or hours is genuinely acceptable, which is far more often than real-time enthusiasts admit. Reach for it when you are moving high volumes, because a single file of a million records is dramatically more efficient than a million API calls. And reach for it when one of the systems simply cannot offer a modern interface, because a file is the interface that every system can produce.

Turn away from file-based integration when the requirement is genuinely real-time, when a user or a downstream process expects an immediate reaction to an event, or when the integration is interactive and needs a live request and response. Those are the domains of APIs and event streaming, not files. Many modern platforms, including Microsoft Dynamics 365 Business Central, expose rich APIs precisely to serve those cases, and choosing between a nightly file export and a live API call is a decision I walk through in the Business Central APIs and integrations guide.

The mistake I see most often is not choosing files when they do not fit, it is being embarrassed by files when they fit perfectly. Teams reach for a heavyweight real-time integration for a problem that is unmistakably a nightly batch, because files feel old-fashioned, and they inherit operational complexity they did not need in exchange for immediacy the business never asked for. The mature move is to match the pattern to the problem without ego. If the data is batch and latency is tolerable, a well-run file feed will outlast and out-reliable the clever alternative, and it will do so with a fraction of the moving parts. For the wider framework of how this pattern sits alongside APIs, messaging and event streaming, return to the enterprise system integration pillar.

9. References

The concepts in this guide draw on established integration literature and widely used standards. The following are solid starting points for going deeper.

• Gregor Hohpe and Bobby Woolf, Enterprise Integration Patterns, Addison-Wesley. The definitive catalogue of integration styles, including the File Transfer pattern, which formalises the model described here.
• RFC 4180, Common Format and MIME Type for Comma-Separated Values (CSV) Files, IETF. The reference specification for CSV quoting and escaping rules that trip up naive parsers.
• RFC 4253 and the SSH File Transfer Protocol drafts, IETF. The basis for SFTP as a secure transport over SSH.
• W3C XML specifications and JSON (ECMA-404 / RFC 8259). The authoritative definitions of the two structured formats covered in the format table.
• Microsoft Learn, Dynamics 365 Business Central developer and integration documentation, for the API-based alternatives referenced in the closing section.

Final thoughts

File-based integration is the pattern people love to call legacy and then quietly keep running, because on the right problem nothing matches it for robustness, universality and sheer operational calm. It trades immediacy for decoupling, and that trade is exactly the one a great many enterprise integrations should be making. The skill is not in avoiding files, it is in recognising the batch-shaped, latency-tolerant, high-volume problems where a file is the honest answer, and then running that file feed with the small disciplines, naming, control files, idempotency, checksums and archiving, that turn a simple idea into a dependable one.

If you are choosing between a file feed and something more real-time, start by asking what the business actually needs rather than what feels modern. Very often the requirement is a clean nightly batch, and the right architecture is the oldest one in the book, done well. Get the pattern match right and the file-based integration you build today will still be quietly moving data long after the fashionable alternatives have been rewritten.

Related reading: Enterprise system integration explained, SFTP integration, CSV integration, Batch vs real-time integration, Business Central APIs and integrations.