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.
Automating the generation of new staff or student user accounts
The basic workflow for the generation of new staff or student accounts is-
- Every 30 minutes a SSIS package kicks off that runs a report on any staff members or students who are marked as having a starting date in the future, the report contains the PersonID (a unique ID number) along with the Forename and Surname of any new starter.
- From the report things like the username, path to personal documents, default password and which mailbox database the user should be attached to are generated, next this information is compared with a SQL database of known current users to see if the user has already been imported or if the user is new (using the Slowly Changing Dimension transform). In addition there is logic built in to detect if a duplicate username has been generated and if so then this is emailed to the ICT Service Desk for manual resolution.
- With the details of the user now in a SQL database a CSV is generated for a PowerShell script which provisions the users AD account, a further CSV is generated which also provisions the users home folder on the file server.
- Next up another CSV is generated which contains the details required to provision the users Exchange email account, this is passed through a remote PowerShell session and the account generated.
- Finally the user is marked as ‘exported’ to prevent their account from being duplicated and an email is sent to the ICT Service Desk containing the users username and password along with a link to any manual tasks that should be carried out.
This SSIS package has been running very well for the past year (which included the generation of ~115 accounts at the same time for the summer Year 7 intake) with only a modification to allow for students with names longer than 100 characters long (yes that one took me by surprise!).
Alerting the service desk when a student or staff member leaves the school
Its an unfortunate fact that its people and not systems that are the biggest security risk to any company/business (nice read here about this); in particular any staff member who leaves the employment of a company without having their computer account disabled could have access to sensitive information as such its important to close these security holes as soon as they appear. In my experience its all too possible for the people who run and manage employment databases can forget (or not think that its important) that ICT services are informed when a person leaves and as such it really helps to tap into these services and generate automated reports when people leave.
As a side note in schools I’ve seen cases where before leaving a school a student passes their login details to other students, perhaps to bypass internet bans or other sanctions.
Here comes SSIS…
- For this package every 30 minutes a report is run (as a CSV) which exports the PersonID, Forename and Surname of any staff member who has a leaving date set, this information is then imported into a SQL database (using a Slowly Changing Dimension transformation to detect any already imported duplicates).
- Using the data stored in the SQL database a CSV is generated and emailed to the service desk with a link to the procedure to carry out whenever a staff member leaves.
- A field is then set in the SQL database that lists the person as already exported thus preventing any further automated notifications about this account being generated.
Originally when working on this package I considered designing it to automatically disable the staff members account on a set date however after consideration I thought it best to leave a human ‘in the loop’ here – particularly as it could in theory allow non IT staff members to maliciously disable accounts!
The process is slightly different for students where the process only emails the service desk once that student leaves the school and not prior.
Importing new staff members into the asset management system
Where I work we use the WASP asset management system, one of the things which drew me to this system is it uses a SQL database to store all of the information on assets and who they are checked out to (compared to web based services which don’t offer a way to programmatically access the information).
For this SSIS package we have-
- Every 30 minutes using the ADO.NET connector the user information in Active Directory is read (including the unique employeeID, Forename, Surname and Job Title) using calculated fields the additional database fields that are used by the asset management system are generated.
- Next using the slowly changing dimension transform this information is compared with the information in the asset management system and any changes or additions are made to the database.
Emailing a report on printing activity to the finance department
Where I work we have a managed approach to printing in that departments are charged a fixed cost per printed page which is charged back to the ICT Services department to cover the cost of consumables. As part of this we use the Equitrac print management solution which logs all of the printers in a SQL database (you can see where I’m going here!).
The sequence for the SSIS package is
- On the last day of every month the SSIS package runs and reads the data from the printing logs table (which includes the department that should be charged along with the cost of the printed material) in the print management database.
- This data is then filtered to only list the current month and year with the results exported into a CSV (stored on a network path) that contains the name of the month and the year.
- An email is then generated with the printing report attached and sent to the finance department to action.
This is by far my favourite SSIS package that I have deployed at the moment, the reason is simple – its a pretty dull monthly task that I’d forget if I wasn’t reminded about.
Generating email distribution lists automatically
I hate to say it but email is here to stay (although ‘enterprise social’ seems to be gaining traction fast of late) and one element of email is distribution lists. This SSIS package is still a work in process and relies on a rather crude method of keeping he lists up to date but for now it works…well kind of 🙂
- Using commandreporter.exe reports are generated which contains the year group that students are in and which house they are in along with reports for the staff members containing which subjects they teach along with the house that staff members are in.
- The current information in locally held SQL tables is then deleted and the information from the reports is imported, a lookup of the PersonID (reports) against employeeID (in Active Directory) is then performed and the usernames imported into the tables.
- This information is then exported into a number of CSVs which in turned is passed to a PowerShell script which deletes all of the users currently in the distribution groups before importing all of the new ones.
As you can tell this is a very messy process, indeed right now this package takes 2 hours to run and is only run once a day overnight. My hope is that in the future I’ll be able to get it to work out the changes to the distribution groups (additions and removals) which will in turn speed up the execution massively.
That’s all folks…
Hopefully this post will spark off some ideas in everyone; maybe one day I’ll have the time to put each process into its own guide but in the meantime if you would like to chat about how this works just let me know through the comments box.