Connecting Carrier SFTP Feeds: What Agencies Get Wrong
Almost every carrier and FMO can drop your production files somewhere (a book-of-business export, a commission statement, a policy status file), and increasingly that somewhere is an SFTP server. Connecting to it sounds like a one-afternoon IT task. In practice, the way you set up that feed determines whether the numbers your agents see are current, complete, and correct, or whether you quietly spend the last week of every month reconciling by hand.
Getting the credentials and paths right the first time
An SFTP connection needs a host, a port, a username, and either a password or an SSH key, plus the remote directory where the carrier actually drops files. The mistake agencies make is treating those as one bundle. Carriers rotate passwords, restrict by IP, and sometimes give you a landing folder that is different from where the useful files live. Store the credentials encrypted, test the connection before you trust it, and confirm the exact path so a scheduled pull is not staring at an empty directory.
AgencyGrid keeps every carrier’s SFTP credentials encrypted at rest with AES-256-GCM envelope encryption, with the master key held only in deployment environment variables. That matters because these are not your credentials to lose. They are the carrier’s, and a leaked SFTP login can expose an entire agency’s book.
Files change shape, and your mapping has to survive it
The second thing agencies underestimate is column drift. One carrier calls it “Annualized Premium,” the next calls it “AnnPrem,” a third splits first and last name into two columns, and any of them can quietly rename a header between file drops. If your ingestion assumes fixed column positions, a single reordering turns a clean import into garbage without an error message.
This is where AI-assisted column mapping earns its keep. Instead of hard-coding positions, AgencyGrid proposes a mapping from the carrier’s headers to your canonical fields (policy number, premium, effective date, writing agent, NPN) and remembers it per carrier so the next drop maps itself. You review the mapping once rather than rebuilding it every time a carrier tweaks a template.
The row you import twice is worse than the row you miss
Carriers re-send files. They post a partial book on Monday and a full book on Friday, or they re-drop yesterday’s file after a correction. If your pipeline appends everything it downloads, you end up counting the same policy two or three times, and your book-of-business totals inflate in ways nobody can explain. Dedupe on a stable key, carrier plus policy number, so a re-sent row updates the existing record instead of spawning a duplicate.
Matching each row to the right agent by NPN is the other half of trust. A policy that lands with no writing agent attached rolls up nowhere, and an agent whose NPN does not match the carrier’s file simply does not see their own production.
Make it a schedule, not a chore
The point of an SFTP feed is that it runs without you. A scheduled worker should connect on a cadence, pull new files, map and normalize them, dedupe, match to agents by NPN, and roll the results up the hierarchy, then leave an append-only record of what it did. That is exactly the pipeline AgencyGrid runs, so your book of business is a live view rather than a monthly export you assemble by hand.