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.
Hi Anders, I am having trouble running this script with SCSM 2012 R2 and I get this error: Couldn't find "Workhours" in Service Manager. Script aborted! (Tip: Is the calendar created in Service Manager? Is the spelling correct?) I have imported it several
times in SCSM SP1 environments without any issues. And I know for a fact that the spelling is correct so I guess it has to do with running the SCSM R2 version? Do you have a ps1 script supported for R2?
Same problem here with SCSM 2012 R2 as Nick W has :(
I've had the same problem because I was using the Service Manager Powershell.
Please use the built-in Powershell in Admin mode (don't forget to import the SMLets Module).
I've changed a bit in the ps file to help on the problem mentioned by Brett Moffett.
[String]$outputDate = Get-date $date -format G
$guid = [guid]::NewGuid()
$text = $calendar.id + ",""" + $timeZone + """,SLACalendarHoliday_$guid,$name,$outputDate,$outputDate"
add-content $file $text -encoding Unicode
It worked for me :)