TransformAlertPriorityProcResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformAlertPriorityProcResource" Accessibility="Public" FileName="TransformAlertPriorityProc.sql"/>

File Content: TransformAlertPriorityProc.sql

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


BEGIN


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


END


GO





DECLARE @Statement NVARCHAR(MAX)





SET @Statement = 


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


AS BEGIN 


SET NOCOUNT ON 





    DECLARE @utc DATETIME = GETUTCDATE()


    DECLARE @errorNumber INT, @errorPriority 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 = 1





    BEGIN TRY


    EXEC dbo.InitializeTransform


        @transformName          = ''TransformAlertPriorityProc'',


        @transformTemplateType  = ''Outrigger'',


        @waterMark              = @WaterMark,


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


        @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          = ''TransformAlertPriorityProc'',


            @transformTemplateType  = ''Outrigger'',


            @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 = ''Inserting into Dimension''


        INSERT INTO dbo.AlertPriority (


			EnumTypeId,


			AlertPriorityValue,


			Ordinal,


			ParentId,


			ID,


			InsertedBatchId,


			UpdatedBatchId


        )


		SELECT DISTINCT


			NULL AS EnumTypeId,


			source.Priority,


			NULL AS Ordinal,


			NULL AS ParentId,


			NULL AS ID,


			@BatchId AS InsertedBatchId,


			0 AS UpdatedBatchId


		FROM DWTemp.TransformAlertPriorityProc_Source1 source


        LEFT JOIN dbo.AlertPriority dest ON


			CAST(source.Priority AS VARCHAR(32)) = dest.AlertPriorityValue


		WHERE dest.AlertPriorityValue IS NULL


		    AND source.Priority IS NOT NULL





        SELECT @Inserted = @Inserted + @@ROWCOUNT





        COMMIT TRANSACTION





        EXEC dbo.UninitializeTransformLoop


            @transformName          = ''TransformAlertPriorityProc'',


            @transformTemplateType  = ''Outrigger'',


            @utc                    = @utc,


            @inserted               = @Inserted,


            @updated                = @Updated,


            @loopCount              = @loopCount,


            @canContinue            = @canContinue OUTPUT





    END -- WHILE(@canContinue = 1)





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformAlertPriorityProc'',


        @transformTemplateType  = ''Outrigger'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Computer'',


        @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" Priority="%d" State="%d" ProcedureName="%s" LineNumber="%d" Task="%s"'',


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





    IF(@@TRANCOUNT > @startTranCount) ROLLBACK TRANSACTION


    


    RAISERROR (@errorFmt, 18, @errorState, @errorNumber, @errorMessage, @errorPriority, @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