The Server Room

Class Server 4 - The saga continues

Today saw a smidgen more progress with our Class Server 4 installation. I managed to get all the classes into the database and all the teacher-class relationships too, as well as entering a few straggler teachers that didn’t have accounts on the network (until today that is). Not perfect, but healthy progress.

BEWARE!

The import procedure that Microsoft suggests Class Server administrators use is, well, rubbish.. behold:

(header rows emboldened)

That mess would have to be created in Excel, saved to a “Microsoft Excel XML Spreadsheet Format” and then imported via the CSProvision tool on the Class Server to add AllisonBrown as Teacher of History 6. Those 6 rows of spreadsheet generate precisely 1 row in the database (!) which looks like:

(those are the primary key values from the Classes and Persons tables)

If you want to add another teacher to another class, it’ll take you yet another 6 rows and because Microsoft made the syntax multi-line, you’ll have to use a whacking great macro if you plan to generate the data automatically. Lame? Lame! Faced with the proposition of adding teachers to well over 900 classes, Microsoft’s recommended method went hurtling binward.

Instead I decided to pull the classIDs and teacherIDs out of SQL Server then create a lookup table in Excel. That let me generate a ClassID - TeacherID mapping from my SIMS.net CSV file, resulting in 2 long columns of numbers. Import that into the SQL Server “MapTeachersToClasses” table manually and Bob’s your uncle! Job done easily in a couple of hours.

The only challenge remaining now is to get the pupils and their pupil-class mappings into CS4. That should be relatively straightforward - it’s only hampered by the lack of a direct link between SIMS.net and Active Directory. For that trick I shall be using the UPN from SIMS.net and creating the mother of all lookup tables with every child in school listed by username, Active Directory SID and SIMS.net UPN. Ungh!

To be continued…