Well done. It's always nice to see a RBAR-free, set-based approach to building tables like this. A few things I would change to improve performance:
1. Make all the columns NOT NULLable...
2. Use smaller numeric data types you have int and bigint for columns where a tinyint or smallint will do the trick. ServiceHoliday could be a bit.
3. Update your logic so that the final table has a primary key and clustered index. When I run this locally the finished table has no keys or indexes.
4. Include some more nonclustered unique and non-unique indexes. I generally add narrow single-column nonclustered indexes on my dim_date table; we add included columns (or new indexes) as needed.
-- Alan Burstein
Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw
Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K
I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001