Description: Check Stage Tables for unprocessed records.
Necessary Modification: Every fact table load you build will need code added to the stored procedure so it becomes part of the audit process. Loading dimensions isn't normally problematic so they are not part of this process.
Before each alert, you need to run a select on a staging table checking for unprocessed records. Then alter the message that message that gets sent based on the staging table being checked.
Below is an example.
SELECT*FROM [eod].[Assets]WHERE Processed =0IF @@RowCount >0BEGINEXEC msdb.dbo.sp_send_dbmail@profile_name ='Monitoring',@recipients = @OperatorEmailAddress,@subject ='Unprocessed Records Exist',@body ='There are unprocessed records in the Assets Staging Table' ;END
USE ODSGODROPPROCEDUREIFEXISTS dbo.usp_CheckForUnprocessedRecordsGOSETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATEPROCEDURE dbo.usp_CheckForUnprocessedRecordsASBEGIN--Update with your operator name.DECLARE @OperatorName sysname=N'YourOperatorName';DECLARE @OperatorEmailAddress NVARCHAR(100) = (SELECT email_address FROM msdb.dbo.sysoperators WHERE [name] = @OperatorName);
--Check for unprocessed recordsSELECT*FROM [eod].[Assets]WHERE Processed =0IF @@RowCount >0BEGINEXEC msdb.dbo.sp_send_dbmail@profile_name ='Monitoring',@recipients = @OperatorEmailAddress,@subject ='Unprocessed Records Exist',@body ='There are unprocessed records in the Assets Staging Table' ;ENDSELECT*FROM [eod].[EODPrices]WHERE Processed =0IF @@RowCount >0BEGINEXEC msdb.dbo.sp_send_dbmail@profile_name ='Monitoring',@recipients = @OperatorEmailAddress,@subject ='Unprocessed Records Exist',@body ='There are unprocessed records in the EOD Prices Staging Table' ;ENDSELECT*FROM [eod].[Exchanges]WHERE Processed =0IF @@RowCount >0BEGINEXEC msdb.dbo.sp_send_dbmail@profile_name ='Monitoring',@recipients = @OperatorEmailAddress,@subject ='Unprocessed Records Exist',@body ='There are unprocessed records in the Exchanges staging table.' ;ENDENDGO