TransformDisplayStringDimResource (Resource)

Element properties:

Source Code:

<Resource ID="TransformDisplayStringDimResource" Accessibility="Public" FileName="TransformDisplayStringDim.sql"/>

File Content: TransformDisplayStringDim.sql

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


	begin


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


	end


go





DECLARE @Statement NVARCHAR(MAX)





SET @Statement=


N'alter procedure dbo.TransformDisplayStringDimProc (@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





    EXEC dbo.InitializeTransform


        @transformName          = ''TransformDisplayStringDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Warehouse$DisplayString'',


        @waterMarkType          = ''DateTime'',


        @sourceTableName        = ''inbound.DisplayString'',


        @utc                    = @utc,


        @source1WM              = @source1WM OUTPUT,


        @source1MaxWM           = @source1MaxWM OUTPUT,


        @batchId                = @batchId OUTPUT





    begin transaction


  merge dbo.DisplayStringDim AS target


  using (		


  			select LTStringId, LanguageCode, DisplayName, [Description],ElementName, SubElementName


				  ,MPElementId, ManagementPackId, ContentReadable, DataSourceId, TimeAdded, LastModified


			 FROM (select LTStringId, LanguageCode, Substring(DisplayName, 1, 3072) AS DisplayName, Substring( [Description], 1, 3072) AS  [Description], ElementName, SubElementName


				  ,MPElementId, ManagementPackId, ContentReadable, DataSourceId, TimeAdded, LastModified,


				  ROW_NUMBER() OVER ( PARTITION BY LTStringId, LanguageCode ORDER BY


					TimeAdded DESC, DWTimeStamp DESC) AS SeqNum


			from inbound.DisplayString


			where DWTimestamp >= @source1WM 


			and DWTimestamp < @source1MaxWM) AS src1


			WHERE SeqNum = 1						


		) as source 


		on (target.LTStringId = source.LTStringId) and (target.LanguageCode = source.LanguageCode)				


  when matched then 		


			update set 


				   DisplayName = isnull(source.DisplayName,''NA''),


				   [Description] = isnull(source.[Description],''NA''),							   


				   ElementName = source.ElementName,


				   SubElementName = isnull(source.SubElementName,''NA''),


				   ContentReadable = source.ContentReadable,


				   TimeAdded = source.TimeAdded,


				   LastModified = source.LastModified,


				   UpdatedBatchId = @BatchId,


				   @Updated = @Updated + 1


  when not matched 


  by target then 


		insert (LTStringId, BaseManagedEntityId, LanguageCode, DisplayName, [Description],ElementName, SubElementName,MPElementId, 


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


		values (source.LTStringId, source.LTStringId, source.LanguageCode, isnull(source.DisplayName,''NA''), isnull(source.[Description],''NA''),


			 source.ElementName,isnull(source.SubElementName,''NA''), source.MPElementId, source.ManagementPackId, 


			 source.ContentReadable, source.DataSourceId, source.TimeAdded, source.LastModified,0,0, @BatchId);





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


  if(@err<>0)


  begin


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


        return


  end


  commit transaction





    EXEC dbo.UninitializeTransform


        @transformName          = ''TransformDisplayStringDimProc'',


        @transformTemplateType  = ''Manual'',


        @waterMark              = @WaterMark,


        @warehouseEntityName    = ''MTV_Microsoft$SystemCenter$Warehouse$DisplayString'',


        @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