This is guest blog post from two of our most prolific community contributors Andreas Baumgarten and Anders Asp. Thanks for sharing with the community guys!
As I already mentioned in my blog post about the new SLA Management in Microsoft System Center Service Manager 2012 (SCSM 2012) it isn’t that convenient to add the holidays to an SLA calendar manually.
For this reason, Anders Asp from Lumagate (Sweden) and I started discussing about an easier way to get the holidays in the calendar.
We discovered the following challenges:
1. “Where we can find a list of holidays for all countries?”
2. “How to get a list of holidays in the SLA calendar?”
The answer for the second challenge was easy to find: SCSM 2012 still offers the option to import data via CSV import.
For the first challenge another Microsoft product comes into play: Microsoft Outlook
You can import holidays of different countries in Microsoft Outlook. The files containing the holidays for import are named “Outlook.hol” and are available in the following folder of Outlook:
C:\Program Files\Microsoft Office\Office14\<YOUR LOCALE IN DECIMAL FORM>\OUTLOOK.HOL
A list with the locales you can find here: http://msdn.microsoft.com/en-us/goglobal/bb964664
Anders created a PowerShell script for generating a CSV file out of a Outlook.hol file.
How it works:
· You need the zip file with the PowerShell script and the format file (xml) for import (http://gallery.technet.microsoft.com/Generate-SCSMHolidaysCSVps1-a32722ce)
· Extract the downloaded zip file
· Put the Outlook.hol file from Outlook in the same folder
· Launch PowerShell and change directory to the folder with your files
· Run the PowerShell script (one line): .\Generate-SCSMHolidaysCSV.ps1 -CSVOutput <Name of outputfile.csv> -HolidayFile <Path to your OUTLOOK.HOL file> -Country <Name of country> -CalendarName <Name of the SLA calendar in SCSM to which you want to add these holidays>
· Import the CSV file in SCSM 2012
The empty SLA calender ...
Run the PowerShell Script ....
.\Generate-SCSMHolidaysCSV.ps1 -CSVOutput GermanHolidays.csv -HolidayFile .\English_OUTLOOK.HOL -Country germany-CalendarName “Demo SLA Calender”
Import the CSV via SCSM 2012 console ...
The result …
It was great fun to work with Anders Asp on this. Thanks a lot for your awesome work!
Thanks Andreas Baumgarten and Anders Asp.
This script works but there is one bug for people not in the USA.
The date format from the HOL file is in the local country format (Like DD/MM/YYYY here in Australia) however SCSM wants it in USA format (MM/DD/YYYY)
It seems to import but on closer inspection Easter is in November in 2014! :-S
I'll write a PS1 script to change the CSV file over, but thought I'd let the community know to watch out for this.