Moodle and Capita SIMS – Database enrolment (a short how to)

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.

SQL server setup

To make everything work you will need a SQL database spun up on a SQL server (in this example its called PortalClasses as we call the Moodle install where I work Portal). Don’t worry about creating tables in that database as SSIS will do that for us.

SSIS setup

Now things get interesting, spin up a new SSIS project with a suitable name and jump through the instructions below. As part of tying things in with the SIMS CommandReporter.exe tool you will need the paths/arguments below, the first is the path to the .exe you will need, the second shows the fields to populate and the their is a demo of how the arguments might appear.

C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe /USER:<yourusername> /PASSWORD:<yourpassword> /SERVERNAME:<servername\sqlinstance> /DATABASENAME:<databasename> /REPORT:”<reportname>” /OUTPUT:”<pathtooutput.csv>” /USER:moodlereports /PASSWORD:N0Tt3llingu /SERVERNAME:SIMSDB10\SIMS2008 /DATABASENAME:OxfordSpiresOX /REPORT:”MoodleTeacherExport” /OUTPUT:”C:\SIMSReports\SIMStoPortal\teachers.csv”

Active Directory setup

Next up you will need the employeeID field set for all of your AD users to match the personID in SIMS, its a long task to do the first time round (especially with 100s+ of users to deal with) however there are many rewards other than just linking SIMS and Moodle. If required you can generate a report in SIMS to show the list of names against the personID filed using the 3rd party integrations > ID.

Moodle setup

In this instance the Moodle server is running on IIS, as before you will need the following strings as part of the guide which are used when setting up a scheduled task to sync the users from the DB. The top line is the path to the installation of PHP that I am using in my Moodle install (you can find this by going to Site administration > Server > PHP info) and the second is the path to the database sync script (you will find this in your moodle installation under enrol).

“C:\Program Files (x86)\PHP\v5.4\php-win.exe” “E:\IIS\Portal2\enrol\database\cli\sync.php”

All done!

Hopefully you will now have a Moodle install which automates the process of adding students and teachers to classes; its also worth noting that this process can be used with other MISs – its just a matter of getting the data out in a report form and feeding it into SSIS.

Please do comment in the box below if you have any ways to improve the process (at some point I will make an article on doing the same for course generation)!