Share via

OneRoster 1.2 CSV formatted files for SDS

  • Article

This article explains how customers can use OneRoster 1.2 CSV formatted data to transform them to SDS v2.1 CSV formatted files. For more information on OneRoster 1.2 CSV, see IMS OneRoster: CSV Binding.

Important

Using this method, you'll export OneRoster 1.2 CSV format from your SIS / SMS, convert your OneRoster 1.2 CSV formatted files into the SDS v2.1 CSV format, then upload the new SDS v2.1 CSV files into SDS for processing. This will need to happen for each subsequent data refresh to be processed by SDS. SDS only supports BULK mode. After your initial upload during the creation of your inbound flow, you'll need to always supply the same set of files for subsequent uploads, regardless of data changes made in the optional files or not. If you do not the system will determine that the records are no longer active and will mark them as such.

Before you get started

Before you start synchronizing with SDS, we recommend reading SDS Overview for a deeper understanding of the core concepts of SDS.

Make sure you meet the following prerequisites:

  • Microsoft 365 Education tenant
  • Global Administrator Permissions

OneRoster v1.2 and SDS v2.1 Overview

Graphic that shows SDS high-level overview.

The following table is to help with SDS v2.1 CSV field data types and how they're used when processing and storing data. The data types are referenced in the mappings below.

Data Type Matching Rule Validation Rule Value Stored
Unique ID * Case Sensitive As received
Enum ** Case Insensitive Match to lower case As camelCase
String Case Sensitive As received
Email To lower case RFC 5532 (3.4.1) To lower case
Date or DateTime ISO8601 ISO8601 ISO8601
Phone E.164 E.164 As received
Boolean Case Insensitive Match to ‘true’ or ‘false’ To lower case

*The record is unique across all records. Globally unique identifier, though it may or may not take the form of a universal unique identifier (UUID).

** Leading zeros ‘0’ missing on incoming value for Grade Level Enum, for example ‘1’, will match defined List of Value (Enum) ‘01’ and will be stored as ‘01’.

** For more information default Enum values, see the Default List of Values.

Expect phone and sms to be in E.164 and + must be included. (Example: +1234567890)

Required: The three files listed below must exist. If you're only managing users and not classes, you only need to supply these files.

  • orgs.csv

  • users.csv

  • roles.csv

Optional / Required: Not required if you're only managing users. If you're also managing classes and class enrollments, you need to provide the two files listed below, along with the three files above. If you're not, don't provide any files with these names.

  • classes.csv

  • enrollments.csv

Optional: The following are optional files and are used to enhance experiences. Not required, unless the files above have data references based on the notes below.

  • academicSessions.csv (If your roles.csv, classes.csv, or courses.csv contains academic session data, provide to avoid error messages when processing data.)

  • courses.csv (If your classes.csv data contains links to courses, provide to avoid error messages when processing data.)

  • demographics.csv

  • relationships.csv

Note

Based on the default OneRoster 1.2 CSV format there is not a way to produce the SDS v2.1 CSV file called 'userFlags.csv'. As such there will not be mapping provided.

Warning

When using OneRoster 1.2 CSV formatted files for SDS, the created SDS v2.1 CSV files must be appropriately formatted including all columns being present, before uploading to SDS. Column headers are case-sensitive. CSV files must be in UTF-8 format and RFC compliant. Also we don't accept line breaks in incoming data. To review and download sample set of SDS V2.1 CSV files, see the SDS GitHub Repository.

Mapping between OneRoster 1.2 CSV to SDS v2.1 CSV

To help, we have provided file and field mappings between both formats to assist you with your transformation.

Warning

All SDS v2.1 CSV required file column headers must exist, and are case-sensitive, or the file will error basic validation for a properly formatted file.

orgs.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
orgs.csv sourcedId Yes GUID orgs.csv sourcedId Yes Unique ID
orgs.csv name Yes String orgs.csv name Yes String
orgs.csv type Yes Enum orgs.csv type Yes Enum
orgs.csv parentSourcedId No GUID Reference orgs.csv parentSourcedId No Unique ID
orgs.csv identifier No String field dropped
orgs.csv status No Enum field dropped
orgs.csv dataLastModified No DateTime field dropped

users.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
users.csv sourcedId Yes GUID users.csv sourcedId Yes Unique ID
users.csv username Yes String users.csv username Yes String
users.csv givenName Yes String users.csv givenName Yes String
users.csv familyName Yes String users.csv familyName Yes String
users.csv email No String users.csv email No Email
users.csv sms No String users.csv sms No Phone
users.csv phone No String users.csv phone No Phone
users.csv activeDirectoryMatchId No String
users.csv password No String value dropped users.csv password No String
users.csv status No Enum field dropped
users.csv dateLastModified No DateTime field dropped
users.csv enabledUser Yes Boolean field dropped
users.csv userIds No List of Strings field dropped
users.csv middleName Yes String field dropped
users.csv identifier No String field dropped
users.csv agentSourcedIds No List of GUID References field dropped
users.csv grades No String field dropped
users.csv userMasterIdentifier No String field dropped
users.csv resourceSourcedIds No List of GUID References field dropped
users.csv preferredGivenName No String field dropped
users.csv preferredMiddleName No String field dropped
users.csv preferredFamilyName No String field dropped
users.csv primaryOrgSourcedId No GUID Reference field dropped
users.csv pronouns No String field dropped
  • familyName, givenName, and email are required for users if using the option for ‘Create unmatched users’ and/or users that have contact relationship roles in relationships.csv.
  • password is no longer used to supply default password for creating new users. If any value is found in this field for a record, the value is dropped.
  • Expect phone and sms to be in E.164 and + must be included.

roles.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
roles.csv userSourcedId Yes GUID Reference roles.csv userSourcedId Yes Unique ID
roles.csv roleType Yes Enum 'primary' to 'true' roles.csv isPrimary No Boolean
roles.csv role Yes Enum roles.csv role Yes Enum
roles.csv beginDate No Date roles.csv roleStartDate No DateTime
roles.csv endDate No Date roles.csv roleEndDate No DateTime
roles.csv orgSourcedId Yes GUID Reference roles.csv orgSourcedId Yes Unique ID
roles.csv sessionSourcedId No Unique ID
lookup and pull from users.csv, primary grade only roles.csv grade No Enum
roles.csv sourcedId Yes GUID field dropped
roles.csv status No Enum field dropped
roles.csv dateLastModified No DateTime field dropped
roles.csv userProfileSourcedId No GUID Reference field dropped
  • Requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file. This also produces a dependency on the orgs.csv file.
  • Requires a reference to the associated org using the 'orgSourcedId' field. This produces a dependency on the orgs.csv file.
  • If passing multiple roles, for the same user and same org, there SHOULD be one, and only one, primary role set to (true) for each org.

classes.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
classes.csv sourcedId Yes GUID classes.csv sourcedId Yes Unique ID
classes.csv title Yes String classes.csv title Yes String
classes.csv schoolSourcedId Yes GUID Reference classes.csv orgSourcedId Yes Unique ID
classes.csv courseSourcedId Yes GUID Reference classes.csv courseSourcedId No Unique ID
classes.csv termSourcedIds Yes List of GUID References classes.csv sessionSourcedIds No Unique ID []
classes.csv status No ENUM field dropped
classes.csv dateLastModified No DateTime field dropped
classes.csv grades No List of Strings field dropped
classes.csv classCode No String field dropped
classes.csv classType Yes ENUM field dropped
classes.csv location No String field dropped
classes.csv subjects No List of Strings field dropped
classes.csv subjectCodes No List of Strings field dropped
classes.csv periods No List of Strings field dropped
  • This requires a reference to the associated school (org) using the 'orgSourcedId' field. This produces a dependency on the orgs.csv file.
  • If passed: This requires a reference to the associated terms (academic sessions) using the 'sessionSourcedIds' field. This produces a dependency on the academicSessions.csv file.
  • If passed: If a class is associated to multiple sessions in the same Academic Year, for example Fall Semester and Spring Semester, you can supply both of the sourcedId for the sessions on the record. Values should be combined into a single string with the values surrounded by quotes and separated by commas.
  • If passed: This requires a reference to the associated course using the 'courseSourcedId' field. This produces a dependency on the courses.csv file. [ ] denotes more than one value can be passed. These values must be in a single string surrounded by quotes and separated by commas.

enrollments.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
enrollments.csv classSourcedId Yes GUID Reference enrollments.csv classSourcedId Yes Unique ID
enrollments.csv userSourcedId Yes GUID Reference enrollments.csv userSourcedId Yes Unique ID
enrollments.csv role Yes Enum enrollments.csv role Yes Enum
enrollments.csv sourcedId Yes GUID field dropped
enrollments.csv status No Enum field dropped
enrollments.csv dateLastModified No DateTime field dropped
enrollments.csv schoolSourcedId Yes GUID Reference field dropped
enrollments.csv primary No Enum field dropped
enrollments.csv beginDate No Date field dropped
enrollments.csv endDate No Date field dropped
  • This requires a reference to the associated class using the 'classSourcedId' field. This produces a dependency on the classes.csv file.
  • This requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.

academicSessions.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
academicSessions.csv sourcedId Yes GUID academicSessions.csv sourcedId Yes Unique ID
academicSessions.csv title Yes String academicSessions.csv title Yes String
academicSessions.csv type Yes Enum academicSessions.csv type Yes Enum
academicSessions.csv startDate Yes Date academicSessions.csv startDate Yes Date
academicSessions.csv endDate Yes Date academicSessions.csv endDate Yes Date
academicSessions.csv schoolYear Yes Year academicSessions.csv schoolYear Yes Enum
academicSessions.csv status No Enum field dropped
academicSessions.csv dateLastModified No DateTime field dropped
academicSessions.csv parentSourcedId No GUID Reference field dropped
  • Expect date values to be in ISO8601 format.

courses.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
courses.csv sourcedId Yes GUID courses.csv sourcedId Yes Unique ID
courses.csv title Yes String courses.csv title Yes String
courses.csv orgSourcedId No GUID Reference courses.csv orgSourcedId Yes Unique ID
courses.csv courseCode No String courses.csv code No String
courses.csv grades No String map to single grade value courses.csv grade No Enum
courses.csv subjects No List of Strings map to single subject value courses.csv subject No Enum
courses.csv schoolYearSourcedId No GUID Reference courses.csv schoolYearSourcedId No Unique ID
courses.csv status No ENUM field dropped
courses.csv dateLastModified No DateTime field dropped
courses.csv subjectCodes No List of Strings field dropped
  • This requires a reference to the associated organization (org) using the orgSourcedId field. This produces a dependency on the orgs.csv file.
  • If passed: This requires a reference to the associated academic session year record using the 'schoolYearSourcedId' field. This produces a dependency on the academicSessions.csv file.

demographics.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
demographics.csv sourcedId Yes GUID demographics.csv userSourcedId Yes Unique ID
demographics.csv birthDate No Date demographics.csv birthDate No Date
demographics.csv sex No Enum demographics.csv sex No Enum
demographics.csv americanIndianOrAlaskaNative No Enum 'true' to 'americanIndianOrAlaskaNative' demographics.csv raceCodes No Enum[]
demographics.csv asian No Enum 'true' to 'asian' demographics.csv raceCodes No Enum[]
demographics.csv blackOrAfricanAmerican No Enum 'true' to 'blackOrAfricanAmerican' demographics.csv raceCodes No Enum[]
demographics.csv nativeHawaiianOrOtherPacificIslander No Enum 'true' to 'nativeHawaiianOrOtherPacificIslander' demographics.csv raceCodes No Enum[]
demographics.csv white No Enum 'true' to 'white' demographics.csv raceCodes No Enum[]
demographics.csv demographicRaceTwoOrMoreRaces No Enum 'true' to 'demographicRaceTwoOrMoreRaces' demographics.csv raceCodes No Enum[]
demographics.csv hispanicOrLatinoEthnicity No Enum 'true' to 'hispanicOrLatinoEthnicity' demographics.csv ethnicityCodes No Enum[]
demographics.csv countryOfBirthCode No Enum demographics.csv birthCountry No String
demographics.csv stateOfBirthAbbreviation No Enum demographics.csv birthState No Enum
demographics.csv cityOfBirth No Enum demographics.csv birthCity No String
demographics.csv status No Enum field dropped
demographics.csv dateLastModified No DateTime field dropped
demographics.csv publicSchoolResidenceStatus No Enum field dropped

Use the horizontal scrollbar to see full mapping.

  • This requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.
  • Multiple fields mapping to 'raceCodes' should be combined into a single string with the values surrounded by quotes and separated by commas.

relationships.csv

OneRoster 1.2 CSV       Mapping Notes SDS v2.1 CSV      
File Name Field Required Type File Name Field Required Type
users.csv sourcedId Yes GUID student user sourcedId value relationships.csv userSourcedId Yes Unique ID
users.csv agentSourcedIds Yes List of GUID References user sourcedId value associated to student relationships.csv relationshipUserSourcedId Yes Unique ID
relationships.csv relationshipRole Yes Enum
  • This requires a reference to the associated user using the 'userSourcedId' field. This produces a dependency on the users.csv file.
  • This requires a reference to the associated user using the 'relationshipUserSourcedId' field. This produces a dependency on the users.csv file.
  • Multiple associations to the same userSourcedId require a separate row in the file.
  • Additionally, familyName, givenName, and email are required for users that have Contact Relationship roles.

Data ingestion with SDS v2.1 CSV

After you generated and validated your SDS v2.1 CSV files, use the following information to ingest SDS v2.1 CSV.