TransformAlertInstanceDimProcResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformAlertInstanceDimProcResource" Accessibility="Public" FileName="TransformAlertInstanceDimProc.sql"/>

File Content: TransformAlertInstanceDimProc.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformAlertInstanceDimProc')


BEGIN


    EXECUTE ('CREATE PROCEDURE dbo.[TransformAlertInstanceDimProc] @WaterMark xml AS RETURN 1')


END


GO





DECLARE @Statement NVARCHAR(MAX)





SET @Statement = 


N'ALTER PROCEDURE [dbo].[TransformAlertInstanceDimProc](@WaterMark XML)


AS BEGIN 


SET NOCOUNT ON 





    DECLARE @utc DATETIME = GETUTCDATE()


    DECLARE @errorNumber INT, @errorSeverity INT, @errorState INT, @errorLine INT, @errorProcedure NVARCHAR(256), @errorMessage NVARCHAR(MAX), @task NVARCHAR(512)


    DECLARE @BatchId INT, @Updated INT = 0, @Inserted INT = 0, @retval INT = -1, @err INT = 0, @startTranCount INT = @@TRANCOUNT, @source1WM DATETIME, @source1MaxWM DATETIME


    DECLARE @rowsToProcess INT = 0, @rejectedRowCount INT = 0, @canContinue BIT = 1, @loopCount INT


    DECLARE @isReconciled BIT = 0





    BEGIN TRY


    EXEC dbo.InitializeTransform


        @transformName          = ''TransformAlertInstanceDimProc'',


        @transformTemplateType  = ''CustomDimension'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$OM$AlertInstance'',


        @waterMarkType          = ''DateTime'',


        @sourceTableName        = ''inbound.AlertStaging'',


        @utc                    = @utc,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT





    WHILE(@canContinue = 1)


    BEGIN


        SELECT @task = ''Initializing Transform loop''


        EXEC dbo.InitializeTransformLoop


            @transformName          = ''TransformAlertInstanceDimProc'',


            @transformTemplateType  = ''CustomDimension'',


            @sourceTableName        = ''inbound.AlertStaging'',


            @utc                    = @utc,


            @targetClassCheck       = '''',


            @batchId                = @batchId,


            @source1WM              = @source1WM OUTPUT,


            @source1MaxWM           = @source1MaxWM OUTPUT,


            @rowsToProcess          = @rowsToProcess OUTPUT,


            @loopCount              = @loopCount OUTPUT





        IF(@rowsToProcess = 0) BREAK;





        BEGIN TRANSACTION


   --     SELECT @task = ''Updating Dimension''


   --     UPDATE dest SET


			--[AlertDescription] = source.[Description],


			--[AlertName] = source.[Name],


			--[AlertProblemGuid] = source.[ProblemId],


			--[IsMonitorAlert] = source.[IsMonitorAlert],


			--[TargetEntityDimKey] = source.[Microsoft.SystemCenter.Datawarehouse.OM.Alert!TargetEntityDimKey],


   --         [IsDeleted] = ISNULL(baseEntityDim.[IsDeleted], 0),


   --         [SourceId] = source.[DatasourceId],


   --         [UpdatedBatchId] = @BatchId


   --     FROM DWTemp.TransformAlertInstanceDimProc_Source1 source


   --     INNER JOIN dbo.AlertDimvw dest ON (source.BaseManagedEntityId = dest.BaseManagedEntityId AND source.DatasourceId = dest.SourceId)


   --     INNER JOIN dbo.EntityDim baseEntityDim ON (source.DatasourceId = baseEntityDim.SourceId AND source.MonitoringObjectId = baseEntityDim.BaseManagedEntityId)


   --     WHERE source.ProblemId = dest.AlertProblemGuid





   --     SELECT @Updated = @Updated + @@ROWCOUNT





        SELECT @task = ''Inserting into Dimension''


        INSERT INTO dbo.AlertInstanceDimvw (


            [SourceId],


            [BaseManagedEntityId],


			[AlertGuid],


            [InsertedBatchId],


            [UpdatedBatchId]


        )


        SELECT DISTINCT


            source.[DatasourceId],


            NULL AS BaseManagedEntityId,


			source.Id AS AlertGuid,


            @BatchId,


            0


        FROM DWTemp.TransformAlertInstanceDimProc_Source1 AS source


        LEFT JOIN dbo.AlertInstanceDimvw dest ON (source.DatasourceId = dest.SourceId AND source.Id = dest.AlertGuid)


        WHERE dest.AlertGuid IS NULL





        SELECT @Inserted = @Inserted + @@ROWCOUNT





        COMMIT TRANSACTION





        EXEC dbo.UninitializeTransformLoop


            @transformName          = ''TransformAlertInstanceDimProc'',


            @transformTemplateType  = ''CustomDimension'',


            @utc                    = @utc,


            @inserted               = @Inserted,


            @updated                = @Updated,


            @loopCount              = @loopCount,


            @canContinue            = @canContinue OUTPUT





    END -- WHILE(@canContinue = 1)





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformAlertInstanceDimProc'',


        @transformTemplateType  = ''CustomDimension'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$OM$AlertInstance'',


        @waterMarkType          = ''DateTime'',


        @source1MaxWM           = @source1MaxWM,


        @batchId                = @BatchId,


        @inserted               = @Inserted,


        @updated                = @Updated





    END TRY


    BEGIN CATCH


    DECLARE @errorFmt       VARCHAR(256)





    SELECT  @errorFmt = N''ErrorNumber="%d" Message="%s" Severity="%d" State="%d" ProcedureName="%s" LineNumber="%d" Task="%s"'',


            @errorNumber = ERROR_NUMBER(), @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE(), @errorLine = ERROR_LINE(), @errorProcedure = ERROR_PROCEDURE() 





    IF(@@TRANCOUNT > @startTranCount) ROLLBACK TRANSACTION


    


    RAISERROR (@errorFmt, 18, @errorState, @errorNumber, @errorMessage, @errorSeverity, @errorState, @errorProcedure, @errorLine, @task)


    RETURN -1


    END CATCH





SET NOCOUNT OFF


END'








IF EXISTS(SELECT * FROM sys.objects WHERE type = 'U' AND name = 'TablePartition' )


BEGIN


	EXEC sp_executesql @Statement


END


GO