TransformManagementPackDimResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformManagementPackDimResource" Accessibility="Public" FileName="TransformManagementPackDim.sql"/>

File Content: TransformManagementPackDim.sql

if not exists (select * from sysobjects where type = 'P' AND name = N'TransformManagementPackDimProc')


	begin


		execute ('create procedure dbo.[TransformManagementPackDimProc] @WaterMark xml as return 1')


	end


go





DECLARE @Statement NVARCHAR(MAX)





SET @Statement=


N'alter procedure dbo.TransformManagementPackDimProc (@WaterMark xml)


as


begin	


set nocount on


	


    DECLARE @utc DATETIME = GETUTCDATE()





    -- Get Metadata


    declare @BatchId int, @Updated int = 0, @Inserted int = 0, @ModuleName nvarchar(128), 


	      @source1WM datetime, @source1MaxWM datetime, @retval int, @err int, @nullguid uniqueidentifier





    set @nullguid = ''00000000-0000-0000-0000-000000000000''





    EXEC dbo.InitializeTransform


        @transformName          = ''TransformManagementPackDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$Base$ManagementPack'',


        @waterMarkType          = ''DateTime'',


        @sourceTableName        = ''inbound.ManagementPackStaging'',


        @utc                    = @utc,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT





    EXEC @err = dbo.InferDatasourceDimProc


        @sourceTableName    = ''inbound.ManagementPackStaging'',


        @columnName         = ''DataSourceId'',


        @filterColumnName   = ''MPLastModified'',


        @minTimeStamp       = @source1WM,


        @maxTimeStamp       = @source1MaxWM,


        @batchId            = @BatchId





    ;with MPList(ManagementPackId, MPIsSealed, MPName, MPVersion, MPKeyToken, DataSourceId, MPCreated, MPLastModified, RowId) as


    (


	    select ManagementPackId, MPIsSealed, MPName, MPVersion, MPKeyToken,


		      DataSourceId, MPCreated, MPLastModified,


	      ROW_NUMBER() over (partition by DataSourceId, ManagementPackId order by MPLastMOdified desc) as RowId


	    from inbound.ManagementPackStaging


	    where DWTimeStamp >= @source1WM 


	    and DWTimeStamp < @source1MaxWM					


    )


    select *


    into #temp1


    from MPList


    where RowId = 1





    merge dbo.ManagementPackDim AS target


    using #temp1 as source 


	    on (target.ManagementPackId = source.ManagementPackId) 


	        and (target.SourceId = source.DataSourceId)


    when matched then 		


		    update set 


			       MPIsSealed= isnull(source.MPIsSealed,0),


			       MPName = source.MPName,


			       MPVersion = source.MPVersion,


			       MPKeyToken = source.MPKeyToken,


			       TimeAdded = source.MPCreated,


			       LastModified = source.MPLastModified,


			       UpdatedBatchId = @BatchId,


			       @Updated = @Updated + 1


    when not matched 


    by target then 


	    insert (ManagementPackId, BaseManagedEntityId, MPIsSealed, MPName, MPVersion, MPKeyToken, 


			      SourceId, TimeAdded, LastModified,UpdatedBatchId, InsertedBatchId, IsDeleted)


	    values (source.ManagementPackId, source.ManagementPackId, source.MPIsSealed, source.MPName, source.MPVersion, source.MPKeyToken,


			      source.DataSourceId, source.MPCreated, source.MPLastModified, 0, @BatchId, 0);





    select @Inserted = @@RowCount - @Updated, @err = @@error





    if(@err<>0)


    begin


        raiserror(''Failed to insert into ManagementPack Dimension'', 16,1)


        return


    end





    if(OBJECT_ID(''tempdb..#temp1'') is not null) drop table #temp1





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformManagementPackDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Datawarehouse$Base$ManagementPack'',


        @waterMarkType          = ''DateTime'',


        @source1MaxWM           = @source1MaxWM,


        @batchId                = @BatchId,


        @inserted               = @Inserted,


        @updated                = @Updated





set nocount off


end'





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


BEGIN


	EXEC sp_executesql @Statement


END


GO