School Data Sync + SIMS - Powershell Script

[nickbro]

Hi all,

 

Spent two days writing this script for SIMS in powershell.

 

What you need to make it run:

 

Server/PC with AD Powershell Scripts

Download and install Azure PowerShell

Download the tool from Microsoft School Data Sync Toolkit

Download and import SIS School.zip

Download and Edit SDS.zip

Follow instructions in SDS.ps1

 

script is provided as is, seems to work for me.

[jslate1980]

Thanks for this. I have a question though. I imported the reports into sims but when looking at the teacher rota the staff Id(Ces third party column), where is data being taken from? Just randomly I have a one member of staff with one number and trying to work out why.

[nickbro]

It's the ID from the SQL database, it was the only thing I could think of. That is stored in the employeeID field in AD

[woody]

Ok thanks to @nickbro who has helped me hit the ground rolling with this. This may also help @MYK-IT who has asked on another post about how you can match up SIMS records with AD / Azure accounts.

 

I have been looking at how to automate as much as possible of this routine task. Interestingly, Bryan at MS who has been taking me through the initial SDS sync said they are looking at a sync tool for SIMS. No ETA on that though.

 

How I have done it. We were already manually entering school email address into SIMS for both staff and students so that means you can create reports similar to Nickbro's above but with the email address in there. If this is the same as UPNs in AD / Azure then your laughing. However, this is prone to human error so we decided to do it the other way round. Carry on reading....

 

We decided to use the 3rd party field 'External Id' in SIMS as the unique ID across the board. We find that Adno is not unique enough, especially when you have more than one Academy syncing into the same o365 tenancy like us, and UPN is only for students not staff. 'External Id' is consistent for students and staff, even supply staff who are in Manage Classroom staff but do not have a personnel record. External Id also gets around guest pupils with no Adno. It is very long and extremely unlikely to be duplicated across several SIMS databases. If you did come across this then you are very unlucky!

 

So getting back to my earlier comment about doing things the other way round, instead of pulling reports from SIMS for the student.csv and teacher.csv, we decided to write the 'External Id' from SIMS into extensionAttribute1 on the AD account. (Whist we were at it, we also decided to put the UPN and Adno in there for students, and the Staff code in there for staff but this isn’t necessary for the point in hand).

 

For this to work, it means that there is a manual job to be done when a new user appears in SIMS, to get the 'External Id' and add that to the attribute in AD. However, not too much of a problem and we could even write it into our user creation powershell script as a switch allowing us to paste it in.

 

Now that you have the External ID in there, you have a match between SIMS and AD. Editing Nickbro's powershell above, it isn't too difficult to create the csv files. The only ones needed to pull directly from SIMS using CommandReporter is the StudentEnrollment and TeacherRoster ones.

 

At this point in time, I am getting an error when trying to upload the csv files into SDS as follows: "StudentEnrollment.csv : Referenced data in one or more rows are not found in source. Field - ID" and "TeacherRoster.csv : Referenced data in one or more rows are not found in source. Field - ID". I will email Bryan @ MS and tell him what's happening.

 

Hopefully the above gives you an idea of how we are matching things up. Any questions, feel free to ask.

 

Thanks

[pcstru]

(Whist we were at it, we also decided to put the UPN and Adno in there for students, and the Staff code in there for staff but this isn’t necessary for the point in hand).

Just to pick up on this small detail : if there is no actual need to process data, it is generally not allowed under the DPA. UPN is particularly problematic in this respect since it is quite tightly controlled. A data subject might be be said to have a reasonable expectation that it is not replicated between systems unnecessarily. In which case it is unlikely to be fair processing as defined by the DPA. Unnecessary replication of data will also turn into an unnecessary cost under the new regulations.

[woody]

Thanks @pcstru for picking up on this. I guess I can get ahead of myself sometimes! I will revisit this in light of the DPA.

[victory2015]

sorry edit sorted it i type the email wrong! Edited February 24, 2017 by victory2015

[nickbro]

check the filter bit for staff, it should read

 

Get-ADUser -searchbase $staffbase -filter 'employeeNumber -like "*" -and UserPrincipalName -like "*@' + $staffemail + '"' -Properties EmployeeNumber | 

 

Also check the staffemail variable at the top

[victory2015]

Thanks alot Nick, also what do i need to do to populate the employeeNumber field in AD, i have salamander so can make it do all sorts of things i just need know what field from sims i need tell it to put in AD?

[nickbro]

that would be from the CES ID from SIMS, you may want to create a report to fill that in

[victory2015]

At this point in time, I am getting an error when trying to upload the csv files into SDS as follows: "StudentEnrollment.csv : Referenced data in one or more rows are not found in source. Field - ID" and "TeacherRoster.csv : Referenced data in one or more rows are not found in source. Field - ID". I will email Bryan @ MS and tell him what's happening.

 

Hopefully the above gives you an idea of how we are matching things up. Any questions, feel free to ask.

 

i am now getting this too, did you solve it?

[beneal]

You are a genius, Nick!