SDS

Synergetic to Microsoft SDS SQL scripts

Chris Rowtcliff

• 5 min read
Synergetic to Microsoft SDS SQL scripts

I have recently rolled out Microsoft School Data Sync (SDS) with Synergetic and have made the scripts we use public in a GitHub repository. All scripts match the Microsoft SDS 2.1 CSV spec. We are a single campus K-12 school and sync years 4 through 12.

Organisations

https://github.com/ChrisRowtcliff/Synergetic-SDS-SQL-Scripts/blob/main/orgs.sql

Orgs.sql maps to orgs.csv. The column sourcedId requires a unique identifier for the organisation, so I am using the ACARA school code for this, as well as getting the school's name from the Synergetic SchoolName config line in the Synergetic Configuration File Maintenance area.

SELECT TOP (1)
    c1.Value AS 'sourcedId',
    c2.Value AS 'name',
    'School' AS 'type',
    NULL AS parentSourcedId
FROM dbo.Config AS c1
JOIN dbo.Config AS c2 ON c1.Key1 = c2.Key1 AND c1.Key2 = c2.Key2
WHERE c1.Key1 = 'ExternalSystem'
    AND c1.Key2 = 'MCEETYA'
    AND c1.Key3 = 'SchoolCodeACARA'
    AND c2.Key3 = 'SchoolName'

Users

https://github.com/ChrisRowtcliff/Synergetic-SDS-SQL-Scripts/blob/main/users.sql

Users.sql maps to users.csv. We have a process that creates and updates our users from Synergetic in to Active Directory ahead of time, so we don't need to provide the optional information for users. The staff or students Synergetic Community Id is used as the unique identifier in the sourcedId column. Just change '@school.edu.au' for your Azure AD UPN suffix and change the StudentYearLevel where clause to capture the student years you need.

SELECT
    [Id] AS 'sourcedId',
    '' AS familyName,
    '' AS givenName,
    [StudentNetworkLogin] + '@school.edu.au' AS 'username',
    [StudentOccupEmail] AS 'email',
    [StudentNetworkLogin] + '@school.edu.au' AS 'activeDirectoryMatchId',
    '' AS 'password',
    '' AS phone,
    '' AS sms
FROM vStudents
INNER JOIN FileSemesters ON FileSemesters.FileSemester = vStudents.FileSemester AND FileSemesters.FileYear = vStudents.FileYear
WHERE FileSemesters.SystemCurrentFlag = 1 AND LEN([StudentOccupEmail]) > 0 AND vStudents.StudentYearLevel >= 4

UNION ALL

SELECT DISTINCT
    vStaff.[StaffId] AS 'sourcedId',
    vstaff.[staffsurname] AS familyName,
    vstaff.[StaffGiven1] AS givenName,
    [NetworkLogin] + '@school.edu.au' AS 'username',
    [StaffOccupEmail] AS 'email',
    [NetworkLogin] + '@school.edu.au' AS 'activeDirectoryMatchId',
    '' AS 'password',
    vStaff.StaffExtension AS phone,
    '' AS sms
FROM vSubjectClassesStaff
INNER JOIN Community ON Community.ID = vSubjectClassesStaff.StaffID
INNER JOIN FileSemesters ON FileSemesters.FileSemester = vSubjectClassesStaff.FileSemester AND FileSemesters.FileYear = vSubjectClassesStaff.FileYear
INNER JOIN vStaff ON vStaff.StaffID = vSubjectClassesStaff.StaffID
WHERE FileSemesters.SystemCurrentFlag = 1 AND vSubjectClassesStaff.StaffID IS NOT NULL AND vSubjectClassesStaff.FileType = 'A' AND EXISTS (
    SELECT 1
    FROM vStudentClasses
    WHERE vStudentClasses.StudentYearLevel >= 4 AND vStudentClasses.ClassCode = vSubjectClassesStaff.ClassCode
);

Classes

https://github.com/ChrisRowtcliff/Synergetic-SDS-SQL-Scripts/blob/main/classes.sql

Classes.sql maps to classes.csv. for the unique identifier in the column sourcedId we are using the format [file year]_[class code]. For orgSourcedId we're using the ACARA school code defined in the org.csv file above. The class title is the class description (name) field appended with the class code in brackets to ensure uniqueness and to enable staff to easily see which classes they have.

Again, we're only syncing classes for years 4 through 12, so change the where clauses if you need to sync something different.

SELECT DISTINCT 
    CAST(vc.FileYear AS varchar) + '_' + REPLACE(vc.ClassCode, ' ', '') AS 'sourcedId', 
    cfg.Value AS 'orgSourcedId', 
    REPLACE(REPLACE(REPLACE(vc.ClassDescription + ' (' + vc.ClassCode + ')', '&', 'and'), ':', '-'), '  ', ' ') AS 'title', 
    CAST(vc.FileYear AS varchar) + 'S' + CAST(vc.FileSemester AS varchar) AS 'sessionSourcedId', 
    '' AS courseSourceID
FROM dbo.vStudentClasses vc
LEFT JOIN dbo.FileSemesters fs ON fs.FileSemester = vc.FileSemester AND fs.FileYear = vc.FileYear
LEFT JOIN dbo.Config cfg ON cfg.Key1 = 'ExternalSystem' AND cfg.Key2 = 'MCEETYA' AND cfg.Key3 = 'SchoolCodeACARA'
WHERE vc.StudentYearLevel >= 4 AND fs.SystemCurrentFlag = 1 AND vc.StopDate IS NULL;

Enrollments

https://github.com/ChrisRowtcliff/Synergetic-SDS-SQL-Scripts/blob/main/enrollments.sql

Enrollments.sql maps to enrollments.csv. This script gets all the student enrolments, with the classSourcedId identifier as constructed in the sourcedId column in classes.sql, the student's Synergetic Id as the userSourcedId (as in users.sql) and the role as 'Student'. This is joined with all the teachers who are added with the same classSourcedId identifier, the staff member's Synergetic Id as their userSourcedId and 'Teacher' as the role.

Again, we're only syncing classes for years 4 through 12, so change the where clauses if you need to sync something different.

SELECT
    CAST(vc1.FileYear AS varchar) + '_' + REPLACE(vc1.ClassCode, ' ', '') AS classSourcedId,
    vc1.StudentId AS 'userSourcedId',
    'Student' as 'role'
FROM vStudentClasses vc1
LEFT JOIN FileSemesters fs1 ON fs1.FileSemester = vc1.FileSemester AND fs1.FileYear = vc1.FileYear
WHERE vc1.StudentYearLevel >= 4 AND fs1.SystemCurrentFlag = 1 AND vc1.StopDate IS NULL

UNION ALL

SELECT
    CAST(vc2.FileYear AS varchar) + '_' + REPLACE(vc2.ClassCode, ' ', '')  AS classSourcedId,
    vc2.StaffID AS 'userSourcedId',
    'Teacher' as 'role'
FROM vSubjectClassesStaff vc2
LEFT JOIN FileSemesters fs2 ON fs2.FileSemester = vc2.FileSemester AND fs2.FileYear = vc2.FileYear
WHERE fs2.SystemCurrentFlag = 1 AND vc2.StaffID IS NOT NULL AND vc2.FileType = 'A' 
  AND EXISTS (
    SELECT 1
    FROM vStudentClasses vc3
    WHERE vc3.StudentYearLevel >= 4 AND vc3.ClassCode = vc2.ClassCode
);

Roles

https://github.com/ChrisRowtcliff/Synergetic-SDS-SQL-Scripts/blob/main/roles.sql

Roles.sql maps to roles.csv. This script returns the userSourceId which is the Synergetic Id for the student or staff member, the Organisation Id for orgSourcedId (the ACARA number defined in orgs.csv), the role, whether the user is a staff or student, the sessionSourceId which is the [FileYear]S[FileSemester], i.e. 2023S2, and the students numerical grade from 04 through 12.

Again, we're only syncing classes for years 4 through 12, so change the where clauses if you need to sync something different.

SELECT
    [Id] AS 'userSourcedId',
    Config.[Value] AS 'orgSourcedId',
    'Student' AS 'role',
    CAST(FileSemesters.FileYear AS varchar) + 'S' + CAST(FileSemesters.FileSemester AS varchar) AS 'sessionSourcedId',
    CASE WHEN LEN(CAST([StudentYearLevel] AS varchar)) < 2 THEN '0' + CAST([StudentYearLevel] AS varchar) ELSE CAST([StudentYearLevel] AS varchar) END AS 'grade',
    '' AS isPrimary,
    '' AS roleStartDate,
    '' AS roleEndDate
FROM vStudents
LEFT JOIN FileSemesters ON FileSemesters.FileSemester = vStudents.FileSemester AND FileSemesters.FileYear = vStudents.FileYear AND FileSemesters.SystemCurrentFlag = 1
LEFT JOIN Config ON Config.Key1 = 'ExternalSystem' AND Config.Key2 = 'MCEETYA' AND Config.Key3 = 'SchoolCodeACARA'
WHERE FileSemesters.SystemCurrentFlag = 1
    AND LEN([StudentOccupEmail]) > 0 AND StudentYearLevel >= 4 

UNION ALL

SELECT
    vStaff.[StaffId] AS 'userSourcedId',
    Config.[Value] AS 'orgSourcedId',
    'Teacher' AS 'role',
    CAST(FileSemesters.FileYear AS varchar) + 'S' + CAST(FileSemesters.FileSemester AS varchar) AS 'sessionSourceId',
    NULL AS 'grade',
    '' AS isPrimary,
    '' AS roleStartDate,
    '' AS roleEndDate
FROM vStaff
LEFT JOIN FileSemesters ON FileSemesters.SystemCurrentFlag = 1
LEFT JOIN Config ON Config.Key1 = 'ExternalSystem' AND Config.Key2 = 'MCEETYA' AND Config.Key3 = 'SchoolCodeACARA'
WHERE vStaff.ActiveFlag = 1
    AND EXISTS (
        SELECT 1
        FROM vStudentClasses
        INNER JOIN FileSemesters ON FileSemesters.FileYear = vStudentClasses.FileYear AND FileSemesters.FileSemester = vStudentClasses.FileSemester AND FileSemesters.SystemCurrentFlag = 1
        WHERE vStudentClasses.StudentYearLevel >= 4 AND vStudentClasses.StaffID = vStaff.StaffID
    )
    AND LEN(StaffOccupEmail) > 0;

Summing Up

Hopefully these scripts will save you some time if you are using Synergetic as a School Management System and need to integrate with Microsoft School Data Sync.

I'm sure these scripts can be optimised, so if you see something that could be done better - feel free to submit a PR on GitHub.