Ellucian Colleague → Salesforce Education Data Architecture

EDA models people as Contact, institutions and administrative units as Account, and the relationships between them as Affiliation and Course_Enrollment__c. The common trap is treating a Colleague person record as a flat Contact and losing the program/term affiliations that EDA reporting depends on. Route both systems through a canonical constituent record and let each map onto it.

Free reference snippet

Yours to use

A working piece from this integration — no sign-up. The full build handles the edge cases, safeguards, and cutover.

// Colleague → Salesforce EDA Field Mapping (Core Entities)

// 1. STUDENT PERSON (STUPERS) → Contact + Account
Student      → Contact
  .PERSON_ID           → Contact.EmployeeNumber
  .FIRST_NAME          → Contact.FirstName
  .LAST_NAME           → Contact.LastName
  .EMAIL_ADDR          → Contact.Email
  .BIRTH_DATE          → Contact.Birthdate
  .ADDR_LINE_1         → Contact.MailingStreet
  .CITY / .STATE / .ZIP → Contact.MailingCity/State/PostalCode

// 2. STUDENT PROGRAM (STUPRGM) → Affiliation
Program      → hed__Affiliation__c
  .STU_ID              → hed__Contact__c (lookup)
  .ACAD_PROG           → hed__Account__c (Academic Program Account)
  .MAJORS[]            → hed__Program__c
  .CLASS_LEVEL         → hed__Class__c
  .PROG_STATUS         → hed__Status__c (Current/Former/Alumni)
  .START_TERM          → hed__Enrollment_Status__c

// 3. ENROLLMENT (STUENRL) → Course Connection
Enrollment   → hed__Course_Enrollment__c
  .STU_ID              → hed__Contact__c
  .CRS_ID              → hed__Course_Offering__c
  .REG_STATUS          → hed__Enrollment_Status__c (Registered/Dropped)
  .CR_GRADE_LETTER     → hed__Grade__c
  .ATTEMPT_HRS         → hed__Credits__c

Implementation pathway

Step by step

How we'd take this from discovery to a production-safe cutover — the phases, the canonical mapping, and the edge cases that bite.

Integration from Ellucian Colleague to Salesforce Education Data Architecture (EDA) requires translating a legacy student information system's relational schema into the platform's account-contact affiliation model. This document defines the implementation pathway from discovery through cutover for a batch-oriented transfer integration.

Implementation Phases

Discovery and Requirements Gathering — Interview academic records and registrar offices; enumerate required entities (constituents, programs, course enrolments), identify reporting obligations (FERPA handling, data retention), and document any custom Colleague extensions or third-party modules in use.
Source Schema Analysis — Profile key Colleague tables: PERSONS, STUDENTS, STUCR (course registration), STUSECT (section assignments), COURSES, PROGRAMS, and STUHS (holds). Document data types, nullability, and legacy codes for academic standing and enrollment status.
Target Data Model Mapping — Map Colleague entities to EDA objects: PERSONS → Contact; PROGRAMS → Affiliation; STUCR/STUSECT → CourseConnection. Design transformation logic for status codes, academic levels, and term dates.
Integration Middleware Development — Build ETL jobs (MuleSoft, SSIS, or equivalent) with read-only Colleague API access (Ellucian Ethos or direct DB). Implement idempotent upsert logic using PERSONS.ID as the external ID on Contact to prevent duplicate creation on re-runs.
Data Cleansing and Validation — Run deduplication checks on email and name; standardize PERSONS.ADDR1/ADDR2 into Contact.MailingStreet. Validate term dates against STUSECT.TERM before loading CourseConnection.EnrollmentStatus.
Testing and UAT — Execute unit tests on field mappings, integration tests with synthetic cohort data, and user acceptance testing with registrar staff validating affiliation accuracy and course connection completeness.
Dry Run and Reconciliation — Load a full historical snapshot into a sandbox; compare record counts by entity, validate Affiliation role counts against source program registrations, and identify gaps before go-live.
Cutover and Activation — Execute incremental delta load on go-live date; activate Salesforce Flows or triggers that maintain EDA relationships; disable Colleague write-back unless bidirectional sync is sanctioned.
Post-Go-Live Monitoring — Schedule daily reconciliation reports comparing EDA counts against Colleague totals for Contacts, Affiliations, and Course Connections; monitor for bulk API limit breaches during delta syncs.

Canonical Field Mapping

Colleague Entity / Field	EDA Object	Target Field	Transform Notes
`PERSONS.ID`	Contact	`External_ID__c` / `EmployeeNumber`	Upsert key; populate `University_ID__c` custom field as canonical.
`PERSONS.FIRST_NAME`, `LAST_NAME`	Contact	`FirstName`, `LastName`	Handle null `FIRST_NAME` (use `PREFIX` or "Student").
`PERSONS.EMAIL`	Contact	`Email`	Use `PERSONS.EMAIL` if present; fallback to `PERSONS.INET`. Deduplicate against existing Contacts.
`STUDENTS.STAT`	Affiliation	`Status__c`	Map Colleague codes (A=Active, L=Leave of Absence, W=Withdrawn) to EDA picklist values.
`PROGRAMS.PROG` + `PROGRAMS.MAJOR`	Affiliation	`Role__c`, `Program__c`	Create Affiliation record per program; handle dual-degree (create two Affiliation records).
`STUCR.COURSE`, `STUCR.SECTION`	CourseConnection	`CourseOffering__c` (lookup), `Contact__c`	Resolve `Course`+`Section` to `Course_Offering_ID__c` in EDA; create if absent.
`STUCR.MARK`	CourseConnection	`Grade__c`	Map numeric or letter grade codes; set `Credits_Earned__c` from `COURSES.CREDITS`.
`STUHS.HOLD_TYPE`	Contact	`Has_Holds__c` (checkbox) or `Holds__c` (long text)	Consolidate multiple holds into semicolon-delimited text; trigger alerts via Flow on insert.

Edge Cases

Duplicate Contacts — Colleague may contain PERSONS.ID duplicates due to merge errors; upsert on external ID silently overwrites rather than creates a new record, causing data loss if ordering is not deterministic. Enforce deterministic sort on PERSONS.ID and log collisions.
Name Changes and Prefixes — PERSONS.NAMECHG stores former names; EDA's Contact supports only a single Name field. Map the current name; store prior names in a custom Previous_Names__c text area to preserve audit history.
Multiple Active Programs — A student may have concurrent enrollments in multiple programs (dual-degree). The integration must generate one Affiliation per program, not overwrite a single Affiliation record.
Term Date Skew — STUSECT.TERM is a date field; EDA CourseConnection links to Term. If the term record does not exist in EDA, the Course Connection insert fails silently with a foreign-key violation. Pre-create all term records before cutover.
Incomplete Historical Enrollments — Legacy STUCR records may lack MARK (grade) for in-progress courses. Map null MARK to Enrollment__c status "Registered" rather than leaving grade blank, which EDA may interpret as a closed record.
Hold Flags Blocking Registration — STUHS holds are often soft-delete flags in Colleague. If a hold was released but the row remains, the integration may incorrectly set Has_Holds__c to true. Validate against a status column on STUHS or the release date field.
Birthdate Normalization — PERSONS.DOB may be stored as text in legacy systems. Parsing failures produce null Birthdate on Contact, triggering validation rule errors if required. Implement pre-load date-format validation with fallback logging.

Cutover

Cutover must be reversible and reconciled. Before go-live, export a full snapshot of EDA data (Contacts, Affiliations, Course Connections) as JSON and store it in a versioned S3 bucket or Git release. On cutover day, execute a final delta load from Colleague capturing all changes since the last sync timestamp; after loading, run reconciliation queries comparing PERSONS.ID counts to Contact.External_ID__c counts, PROGRAMS counts to Affiliation counts by role, and STUCR row counts to CourseConnection counts per term. If counts diverge beyond a defined threshold (typically >0.5%), halt and investigate before activating any Flows that propagate EDA data downstream to Engage or Experience Cloud. Rollback procedure: restore the pre-cutover JSON snapshot to EDA via the Data Loader, re-enable the legacy Colleague reporting system, and resume delta sync after root-cause analysis.

What a full implementation includes

Canonical mapping between Ellucian Colleague and Salesforce EDA, to the field level.
The edge cases that corrupt data at cutover — identified, handled, and tested.
Production-safe rollout: reversible, phased, with reconciliation checks.
Handover documentation your team can operate from.