TransformFIMSequenceDimResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformFIMSequenceDimResource" Accessibility="Public" FileName="TransformFIMSequenceDim.sql"/>

File Content: TransformFIMSequenceDim.sql

--If [dbo].[TransformFIMSequenceDimProc] exists then drop it


IF  EXISTS


(


SELECT [name]


FROM sys.objects 


WHERE object_id = OBJECT_ID(N'[dbo].[TransformFIMSequenceDimProc]') 


    AND type in (N'P', N'PC')


)


BEGIN


    DROP PROCEDURE [dbo].[TransformFIMSequenceDimProc];


END


GO





CREATE PROCEDURE [dbo].[TransformFIMSequenceDimProc] (@WaterMark xml)


AS


BEGIN	


    SET NOCOUNT ON


    SET XACT_ABORT ON


    SET ANSI_NULLS ON


    SET QUOTED_IDENTIFIER ON





    DECLARE @errorNumber    INT,


        @errorSeverity      INT,


        @errorState         INT,


        @errorLine          INT,


        @errorProcedure     NVARCHAR(256),


        @errorMessage       NVARCHAR(MAX),


        @startTranCount     INT,


        @task               NVARCHAR(512),


        @BatchId            INT, 


        @Inserted           INT, 


        @Updated            INT, 


        @MaxSequenceTimestamp    DATETIME, 


        @SequenceTimestamp   DATETIME, 


        @MinDateKey         INT,


        @MinSequenceCount	INT,


        @SequenceCount		INT;





    SET @startTranCount = @@TRANCOUNT;


    SET @Inserted = 0; --Not in Generated transform


    SET @Updated = 0; --Not in Generated transform


    SET @MinSequenceCount = 100000; --Minium Number of rows in the sequence table





    BEGIN TRY


    


        SELECT * INTO #wm FROM [etl].[ShredWaterMark](@WaterMark)	





        SELECT TOP 1 


            @SequenceTimestamp = [WaterMark], 


            @BatchId = [BatchId]


        FROM #wm 


        WHERE #wm.[WarehouseEntityName] = N'MTV_FIMDW$FIMSequence';


        


        DECLARE @Source UNIQUEIDENTIFIER;


        DECLARE @SequenceInbound INT = 0;





        SELECT TOP 1 @Source = SourceID FROM FIMEntityDimvw WHERE SourceId <> '00000000-0000-0000-0000-000000000000';


        SELECT @BatchId = ISNULL(@BatchId, 1);


        


        SELECT @SequenceCount  = COUNT(*) FROM [dbo].[FIMSequenceDimvw] WHERE FIMSequenceID IS NOT NULL;





        IF @SequenceCount = 0 


            AND CONVERT(NVARCHAR(50), @Source) <> ''


            AND @Source IS NOT NULL	


        BEGIN


            WHILE @SequenceInbound < @MinSequenceCount 


            BEGIN


                INSERT INTO [dbo].[FIMSequenceDimvw] 


                    ( [BaseManagedEntityId],


                    [EntityDimKey],


                    [FIMSequenceID],


                    [InsertedBatchId],


                    [IsDeleted],


                    [SourceId],


                    [UpdatedBatchId]) 


                VALUES 


                    (


                    NEWID(),


                    @SequenceInbound + 1,


                    @SequenceInbound,


                    @BatchId,


                    0,


                    @Source,


                    @BatchId);


                


                SELECT @SequenceInbound  =  @SequenceInbound + 1;


            END


        END





        SELECT @WaterMark = 


            (SELECT [ModuleName] AS "@ModuleName",


                [ProcessName] AS "@ProcessName",


                @BatchId AS "@BatchId",


                (SELECT  DISTINCT [WarehouseEntityName] AS "@WarehouseEntityName",


                        [WarehouseEntityTypeName] AS "@WarehouseEntityTypeName",


                        [EntityGuid] AS "@EntityGuid",


                        CASE [WarehouseEntityTypeName] 


                            WHEN 'Inbound' THEN 'DateTime'


                            ELSE 'BatchId' END AS "@WaterMarkType",


                        [WaterMark] as "@WaterMark"


                    FROM #wm			  


                    FOR XML PATH('Entity'), TYPE)


        FROM  (SELECT  DISTINCT [ModuleName], [ProcessName] FROM #wm) [a]	


        FOR XML PATH('Module'), TYPE);





        SELECT @WaterMark as WaterMark, 


            @BatchId as BatchId, 


            @Updated as UpdatedRowCount, 


            @Inserted as InsertedRowCount;





    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;


        


        IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL


        BEGIN


            DROP TABLE #Temp1;


        END





        IF OBJECT_ID('tempdb..#Temp2') IS NOT NULL


        BEGIN


            DROP TABLE #Temp2;


        END








        RAISERROR (


            @errorFmt,


            @errorSeverity,


            @errorState,


            @errorNumber,


            @errorMessage,


            @errorSeverity,


            @errorState,


            @errorProcedure,


            @errorLine,


            @task);





        RETURN -1;


    END CATCH





    SET NOCOUNT OFF


    SET XACT_ABORT OFF


    SET ANSI_NULLS OFF


    SET QUOTED_IDENTIFIER OFF





END





GO