VTS Third-Party Data Pipeline
An automated pipeline that receives lease and space data from third-party managers JLL and Savills over SFTP, lands it on Oxford's file storage via Azure Data Factory, and uses SSIS to package and deliver it to VTS.
Overview
- Project Name: VTS Third-Party Data Pipeline
- Project Type: Automated third-party data ingestion pipeline
- Data Types: Leases and spaces
- Source: Third-party property managers (JLL, Savills, and others), via SFTP
- Target System: VTS
- Tech Stack: Azure Data Factory, SFTP, SSIS, VTS
- Status: Delivered
JLL and Savills manage a portion of Oxford's third-party building portfolio, and part of that relationship means keeping VTS current with their own lease and space data. Rather than have each vendor push directly into VTS, or fall back on manual file handling, this pipeline gives every third-party manager one consistent path: drop lease and space files on an SFTP, and the data moves automatically from there until it lands in VTS.
Architecture
End-to-End Flow
Third-party managers upload their lease and space files to a designated SFTP location. Azure Data Factory picks the files up from there and lands them on Oxford's own file storage (NFS). From that point, SSIS packages take over — packaging the data appropriately and delivering it to VTS's own SFTP endpoint, where VTS ingests it.
Pipeline: third-party SFTP upload, through Azure Data Factory and Oxford's file storage, to SSIS and VTS
Step-by-Step Handoff
The flow above shows the pipeline's shape; the sequence below traces what happens to a single file, in the order it actually happens.
Why This Design
Azure Data Factory's job here is narrow on purpose: move files from the external SFTP drop zone to Oxford's internal storage, and nothing else. It doesn't parse the files or know what's inside them — that's SSIS's job, once the files have already landed. Splitting the pipeline this way keeps two different concerns apart: getting a file off a vendor's server and onto Oxford's network is a different problem from understanding what that file means, and either half can change without the other needing to.
This pipeline only moves data in one direction — from third-party managers into VTS. A separate process runs the other way: VTS Third-Party Building Mapping pulls Oxford's VTS portfolio back out through VTS's own API on a daily basis, to reconcile third-party-managed buildings against JD Edwards.
Key Features
- Single Intake Path for Multiple Vendors: JLL, Savills, and any future third-party manager use the same SFTP-based submission path, rather than each vendor needing its own bespoke integration.
- Azure Data Factory Handoff: ADF bridges the external SFTP drop zone and Oxford's internal file storage, decoupling receiving the files from processing them.
- SSIS Delivery to VTS: SSIS packages handle the final leg — packaging the landed files and delivering them to VTS's own SFTP endpoint.
Technologies Used
Bridges the external SFTP drop zone and Oxford's internal file storage, moving files from third-party managers without manual intervention.
The intake channel from third-party managers, and separately the delivery channel into VTS at the end of the pipeline.
Packages the files once they've landed on Oxford's file storage and delivers them to VTS's SFTP endpoint.
The destination platform that ultimately receives and ingests the third-party lease and space data.
Outcome
JLL and Savills — and any future third-party manager onboarded onto the same pattern — now have one consistent, automated way to keep their lease and space data current in VTS, with no manual file handling required between drop-off and delivery.