TransformAlertCustomFieldDimProcResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformAlertCustomFieldDimProcResource" Accessibility="Public" FileName="TransformAlertCustomFieldDimProc.sql"/>

File Content: TransformAlertCustomFieldDimProc.sql

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


BEGIN


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


END


GO





DECLARE @Statement NVARCHAR(MAX)





SET @Statement = 


N'ALTER PROCEDURE [dbo].[TransformAlertCustomFieldDimProc](@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          = ''TransformAlertCustomFieldDimProc'',


        @transformTemplateType  = ''CustomDimension'',


        @waterMark              = @WaterMark,


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


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


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


        INSERT INTO dbo.AlertCustomFieldDimvw (


            [SourceId],


			[CustomFieldValue],


            [InsertedBatchId],


            [UpdatedBatchId]


        )


        SELECT DISTINCT


			AlertCustomFields.DatasourceId,


			AlertCustomFields.CustomFieldValue,


            @BatchId,


            0


        FROM (


			SELECT DatasourceId, CustomField1 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField2 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField3 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField4 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField5 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField6 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField7 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField8 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField9 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1 UNION


			SELECT DatasourceId, CustomField10 AS CustomFieldValue FROM DWTemp.TransformAlertCustomFieldDimProc_Source1


        ) AS AlertCustomFields


        LEFT JOIN dbo.AlertCustomFieldDimvw dest ON (AlertCustomFields.CustomFieldValue = dest.CustomFieldValue)


        WHERE dest.AlertCustomFieldDimKey IS NULL


			AND AlertCustomFields.CustomFieldValue IS NOT NULL





        SELECT @Inserted = @Inserted + @@ROWCOUNT





        COMMIT TRANSACTION





        EXEC dbo.UninitializeTransformLoop


            @transformName          = ''TransformAlertCustomFieldDimProc'',


            @transformTemplateType  = ''CustomDimension'',


            @utc                    = @utc,


            @inserted               = @Inserted,


            @updated                = @Updated,


            @loopCount              = @loopCount,


            @canContinue            = @canContinue OUTPUT





    END -- WHILE(@canContinue = 1)





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformAlertCustomFieldDimProc'',


        @transformTemplateType  = ''CustomDimension'',


        @waterMark              = @WaterMark,


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


        @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