SQL Server
Microsoft SQL Server Integration Services (SSIS) is a powerful tool that comes with any of the paid editions of SQL server. By design its main function is to extract, transform and load data into databases however by mixing in CSV exports and PowerShell commands it can help automate various workflows.
Although I won’t delve into too much detail in this post I’ll take a look at what is possible in particular-
- Automating the generation of a new staff or student user account
- Alerting the service desk when a student or staff member leaves the school
- Importing new staff members into the asset management system (to assign school resources to them)
- Emailing a report on printing activity to the finance department
- Generating email distribution lists automatically
In the majority of businesses (and in all schools) there will be some kind of central database which stores information on employees/staff and in schools students. Where I work this is Capita SIMS, the database its self comprises of a great number of tables however its possible to avoid rooting through the tables to find the data you want by using the commandreporter.exe application. Continue reading
One of the holy grails of Moodle is having it such that students are added to the right courses in an automated way. This becomes particularly true if you have individual courses for each and every class each of which could have up to 30 enrolments to go through (just far to many to do using manual methods).
Moodle has a number of ways to automate the process out of the box and my favourite way at the moment is using an external database…
So in this post I will show how to use SIMS reports (generated using CommandReporter.exe) to populate student and teacher enrolments in courses as part of a Moodle install using the External database enrolment plugin (more on this here – https://docs.moodle.org/27/en/External_database_enrolment).
Pre requisites…
- First up you will need to know your way around Capita SIMS (in particular creating custom reports) as well as the basics of SQL server management (in particular adding a database to an instance) and Microsoft SQL Server Integration Services (there is a great video series on SSIS here – https://www.youtube.com/playlist?list=PLNIs-AWhQzcmPg_uV2BZi_KRG4LKs6cRs).
- Next you will need a SQL server running Standard edition or higher (this gives you access to SSIS as per http://msdn.microsoft.com/en-gb/library/cc645993.aspx), if you only have Express edition installs in your environment then there isn’t much point in following this guide until you do.
- This guide also assumes that you are using LDAP authentication in your Moodle site and that you have your course lists already populated with the course shortname the same as the course name as it appears in SIMS (it is possible to generate courses using the Database Enrolment method but that’s something for another guide).
- Finally you must have the SIMS.net client (which includes CommandReporter.exe) installed on the SQL server from which you will be running the job to get the class lists into Moodle (more on this a little later).
Capita SIMS setup
For best results create a new SIMS user that will be used exclusively for your Moodle Reports, then login with that user and follow the instructions below.
Has this page helped you? Was it worth the cost of a Coffee? If so click here to donate £1.80 to the myworldofit.net coffee fund via PayPal.