Transform.Common FAIL - Error 4457 - The attempted insert or update of the partitioned view failed because the value of the partitioning column does not belong to any of the partitions

Question

Version: SCSM 2012 R2 UR9
Problem: Data Warehouse Transform.Common Job FAILS and therefore the DataWarehouse is no longer Archiving data from the LIVE ServiceManager Database. This means reports aren't getting updated and SCSM is failing to follow ITIL standards.

This problem started Dec 30th at 6pm.

Troubleshooting Notes:

Restored Data Warehouse Databases back to Dec 27th. Transform.Common can run fine as long as the Extract_SCSM and Extract_DW_SCSM jobs do not pull in the newer data from Dec 30th onwards. On my first go I ran these jobs and after doing all the below it failed.... DO NOT RUN ANY JOBS until you do the below fixes.

Disabled Job Schedules.

DateDim tables were already expanded to 2075 using the PopulateDateDim function

Found the DateKey row was missing of 20201231 (Dec 31 2020), manually added it in the database DateDim tables

--USE [DWRepository];  
--USE [DWDataMart];  
--USE [CMDWDataMart];  
--USE [OMDWDataMart];  
  
INSERT INTO [dbo].[DateDim]  
VALUES (20201231,'2020-12-31 00:00:00','Thursday',31,53,'December',  
		12,2020,'Q4','Q2','6',2021,0,1,1);  

Then noticed constraints on Dec_2020 tables were wrong, the end date range should be the 31st, not the 30th,
So created and ran a script to fix that on the 4 databases that have these Dec_2020 tables.

--USE [DWRepository];  
--USE [DWDataMart];  
--USE [CMDWDataMart];  
--USE [OMDWDataMart];  
  
declare @objName sysname, @newChk as nvarchar(max), @tableName as nvarchar(255);  
declare @statement1 as nvarchar(max), @statement2 as nvarchar(max), @statement3 as nvarchar(max);  
declare @dateRange as nvarchar(max) = '([DateKey]>=(20201201) AND [DateKey]<=(20201231))';  
declare c cursor local FORWARD_ONLY READ_ONLY for  
select name from sys.sysobjects where xtype='C' and name like '%_2020_Dec_Chk' escape '\' order by 1  
open c; while 1=1 begin; fetch c into @objName if @@FETCH_STATUS<>0 break;  
  
set @tableName = replace(@objName,'_Chk','');  
  
--Now create our drop and create constraint query using the names of our table and constraint  
set @statement1 = 'ALTER TABLE [dbo].[' + @tableName + '] DROP CONSTRAINT [' + @objName + '];';  
set @statement2 = 'ALTER TABLE [dbo].[' + @tableName + '] WITH CHECK ADD CONSTRAINT [' + @objName + '] CHECK (' + @dateRange + ');';  
set @statement3 = 'ALTER TABLE [dbo].[' + @tableName + '] CHECK CONSTRAINT [' + @objName + '];';  
  
--Shows what statements will be executed  
--print @statement1;  
--print @statement2;  
--print @statement3;  
  
--Execute the statements  
exec(@statement1);  
exec(@statement2);  
exec(@statement3);  
  
end; close c; deallocate c;  

Then ran DWMaintenance on DW Management Server using PowerShell Command: Start-SCDWJob -JobName DWMaintenance

It took around 2 hours, confirmed my constraints and my datekey of 20201231 was still correct, it was.

Ran the Extract_SCSM and then Extract_DW_SCSM jobs to test out if DWStagingAndConfig can handle the newer data.

Ran Transform.Common

It failed

Reset Failed Job using SQL on DWStagingAndConfig

USE DWStagingAndConfig;  
UPDATE Infra.WorkItem SET StatusId = 6 WHERE BatchId = '<BatchId OfJob>'  
EXEC Infra.CreateBatch 'Transform.Common'  
  
--We get the BatchId of Transform.Common by using Get-scdwjob powershell command  

Still Failed

Went on Microsoft Forums and saw others were having the exact same problem from Dec 30th with the same error of 4457 not ARCHIVING data from LIVE database.

Repeated my steps by restoring earlier Dec 27 versions but without running the Extract Jobs first, instead I applied all my SQL fixes first and followed my steps....

Then Transform.Common completed successfully and I ran Load.Common and it also completed successfully without fails.

Nevertheless, this product is still an embarrassment and Microsoft still needs to fix their faulty product.
It is not fair to expect the customer to phone Microsoft up and pay them to fix a problem that is not the customers fault at all and something that shouldn't have happened in the first place. What a joke at the customers expense.