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