JD Edwards to VTS Integration
The primary data source populating VTS — validating and syncing lease and property data out of JD Edwards before it reaches the platform.
Overview
- Project Name: JD Edwards to VTS Integration
- Project Type: Validated ERP-to-platform data sync
- Source: JD Edwards (JDE)
- Target System: VTS
- Tech Stack: JD Edwards (JDE), SSIS (SQL Server Integration Services), SQL Server, SFTP, VTS
- Status: Delivered
JD Edwards is Oxford's system of record for lease and property data, and VTS depends on that data being both current and correct to be useful for leasing. This integration is the primary channel that keeps the two in sync — it pulls the relevant JDE data, runs it through validation, and only then sends it on to VTS, so VTS reflects real, checked data rather than a raw mirror of JDE. Later Symphony/Yardi planning explicitly aimed to preserve this same nine-file contract even after JDE itself was replaced — the integration's shape was treated as worth keeping, not just its source system.
The integration covers property, building, space/unit, lease, rent, option, and tenant-occupancy synchronization, plus the leasing inventory VTS presents from all of it — each dataset exported according to VTS's own templates and mappings, table by table.
Architecture
Getting JDE's data into a shape VTS can use starts with a custom export: a JDE process writes nine CSV files, each already in the layout VTS specified for its own import so nothing needs reshaping on their end. The files land on a NAS staging share — G:\JDEfiledownload\VTS\PROD_OUT in production, with separate DEV and QA locations — before anything else touches them. The exporter writes them one at a time rather than all at once — the gap between the first file finishing and the last one landing can run up to 40 minutes.
A SQL Agent job — 2300_VTS_Export_JDE_files — runs once a day and drives three SSIS packages in sequence. The first, Load_JDE_Data.dtsx, reads all nine files off the NAS share and loads the raw data into the VTS_Import_Prod database's JDE_Load schema, one table per file, preserving the source structure exactly as JDE wrote it.
The second, Validate_JDE_Data.dtsx, archives the previous run's files, then hands every loaded record to the validation framework — a central stored procedure, [JDE_Load].[Validation_000_Run_All], that runs roughly 29 individual rules covering mandatory fields, data quality, constraints, business rules, and missing values (a bad postal code is one documented example that trips it). Each record comes out marked 0 (pass) or -1 (fail), with a failing record's rule name recorded against it, and the package extracts both outcomes back out to CSV — one file per table, under its original filename.
The third, Upload_JDEFiles.dtsx, transfers the validated exports to VTS's SFTP — the handoff point this integration's own responsibility ends at. From there, VTS's own job picks the files up and updates or creates the records they describe — except buildings. A new building is never created through this pipeline; VTS bills by square footage, so adding one is a deliberate, separate onboarding step, not something a routine data sync should be able to trigger on its own. Failing records never reach that handoff at all: they're emailed to a leasing distribution list instead, and it's the leasing team's job to fix whatever the rule caught before the next day's export tries again. A parallel pipeline handles the equivalent flow for third-party-managed buildings — see VTS Third-Party Data Pipeline — with the same building-creation exception.
Nine JDE exports, three SSIS stages (load, validate, upload), one validation framework: on to VTS, or back to leasing to fix.
Business Rules & Exclusions
The export doesn't send everything JDE has — certain record types are excluded by design: Unit Type SA (Service Agreement), certain storage categories, sublease types, and residential leases. Storage units and leases can be conditionally included for certain retail business-unit classifications. JDE also has an explicit "X" status that marks a record as excluded from the VTS extract outright, for cases the standard exclusion rules don't already cover.
Identifier Mapping
Because JDE and VTS each have their own identifiers, keeping records lined up across both systems takes its own mapping layer, maintained in three parts.
- Property Mapping: ties an Oasis Building Number and Business Unit to a PropertySourceID and VTS Property ID; a stored procedure, [VTS_Import_Prod].[Mapping].[UniqueID_VTS_Property_Mapping], updates these mappings automatically whenever new portfolio data comes back from VTS.
- Lease Mapping: keeps JDE Lease IDs and VTS Lease IDs pointing at the same lease, so downstream systems can reference either one consistently.
- Space Mapping: keeps Oxford Unit IDs and VTS Space IDs aligned, for the same reason — downstream portfolio and leasing integrations depend on it.
Key Features
- Nine-File Custom Export: JDE exports directly into the CSV layout VTS specified, landing on a NAS staging share — the files are written one at a time and can take up to 40 minutes end-to-end.
- Daily SSIS Load: A SQL Agent job runs three SSIS packages once a day, starting with a load into the VTS_Import_Prod database's JDE_Load schema, one table per file.
- Validation Against ~29 Rules: every record is run through a stored procedure covering mandatory fields, data quality, constraints, business rules, and missing values, and marked 0 (pass) or -1 (fail) with the failing rule's name recorded against it.
- Split Delivery: passing records are extracted to CSV and uploaded to VTS's SFTP; failing records are extracted the same way but emailed to a leasing distribution list to fix instead.
- Buildings Excluded by Design: this pipeline updates and creates lease and property records, but never creates a new building — VTS bills by square footage, so onboarding one is a deliberate, separate step.
Technologies Used
The source ERP system holding the lease and property data that ultimately populates VTS.
Runs the daily SQL Agent job's three packages against JDE's nine export files: load, validate, and upload.
Hosts VTS_Import_Prod — the JDE_Load schema the daily load lands in and validation runs against.
Delivery channel for validated records — the same channel VTS's own ingest job reads from.
The destination platform that depends on this integration for accurate, current property and lease data.
Operations & Support
Monitoring watches for constraint-related errors specifically — the record type most likely to get silently dropped from a JDE extract — alerting when the number of constraint violations grows past a configured threshold, and again on a recurring cycle for anything that's stayed unresolved.
- JDE Export Issues: owned by the JDE team.
- Validation Errors: owned jointly by the JDE team and the business.
- Property Onboarding: owned jointly by the business and the JDE team.
- VTS Enhancements: owned by the Corporate Leasing team.
- Access Requests: owned by SRE.
Downstream Consumption
This integration isn't the only thing reading and writing VTS_Import_Prod. Separate portfolio-loading processes pull Oxford's VTS portfolio back out through VTS's own API, build identifier mappings from it, and populate the same VTS_Import_Prod structures this pipeline writes to — feeding the corporate site's leasing-inventory pages and cross-system VTS/JDE/Oasis mapping. It's the same shape of process VTS Third-Party Building Mapping depends on to reconcile third-party-managed buildings against JD Edwards, pulling the VTS portfolio back out on its own daily schedule.
Architecture Assessment
Strengths
- Clear system-of-record ownership — JDE is authoritative, VTS is not, and the pipeline never blurs that line.
- An explicit integration contract: the same nine files, in the same layout, whether the source behind them is JDE or, eventually, Yardi.
- A dedicated validation layer that keeps bad data from ever reaching VTS in the first place.
- Auditability, since every run archives its source files before processing them.
- A file-based, SFTP-delivered handoff that keeps JDE and VTS fully decoupled from each other.
- A real identifier-mapping strategy, so records stay addressable from either system.
- Monitoring and alerting that specifically watches for constraint failures, not just outright job failures.
Risks
- Heavy dependence on a file-based integration — if a file's shape changes upstream, the whole contract breaks.
- A hard dependency on the NAS share's availability; if it's down, nothing moves.
- All nine files have to generate correctly every day — a partial export is still a broken one.
- Support ownership is split across multiple teams, with no single owner for the whole pipeline.
- Cross-system identifier mapping adds real complexity on its own.
- A validation failure blocks a record from reaching VTS until someone on the leasing team fixes it — there's no automatic retry path around a bad record.
Outcome
VTS stays aligned with JDE without manual reconciliation. Every record is checked against a roughly 29-rule validation framework before it's allowed through, failures go straight back to the leasing team with the specific rule they need to fix, and identifier mapping keeps both systems addressable from either side. The one thing this pipeline deliberately never automates — creating a new building — stays a manual step, keeping VTS's square-footage-based billing accurate.