IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformVirtualNicSnapshotFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformVirtualNicSnapshotFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.TransformVirtualNicSnapshotFactProc (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
-- 'Step 2: Deleting records from source, which already exist in the SnapShotFact with same ModifiedDate'
SELECT @task = 'Deleting records from source, which already exist in the SnapShotFact with same ModifiedDate'
DELETE source
FROM DWTemp.TransformVirtualNicSnapshotFactProc_Source1 source
INNER JOIN dbo.VirtualNicDimvw d
ON source.BaseManagedEntityId = d.BaseManagedEntityId
INNER JOIN dbo.VirtualNicSnapshotFactvw SnapShotFact
ON source.LastModified = SnapShotFact.LastModifiedDate
AND d.VirtualNicDimKey = SnapShotFact.VirtualNicDimKey
-- 'Step 3: Copying Records into a temp Table'
SELECT @task = 'Copying Records into a temp Table'
SELECT
d.VirtualNicDimKey,
source1.[LastModified],
source1.[Microsoft.SystemCenter.VirtualMachineManager.Network.VNic!IPv4AddressType] AS IPv4AddressType,
source1.[Microsoft.SystemCenter.VirtualMachineManager.Network.VNic!IPv6AddressType] AS IPv6AddressType
INTO DWTemp.TransformVirtualNicSnapshotFactProc_Source2
FROM DWTemp.TransformVirtualNicSnapshotFactProc_Source1 source1
INNER JOIN dbo.VirtualNicDimvw d
ON source1.BaseManagedEntityId = d.BaseManagedEntityId
-- 'Step 4: Update source to move early arriving records to next batch'
SELECT @task = 'Update source to move early arriving records to next batch'
UPDATE source
SET
DWTimeStamp = GETUTCDATE(),
RejectedCount = ISNULL(RejectedCount, 0) + 1,
BatchId = @BatchId
FROM inbound.MTV_Microsoft$SystemCenter$VirtualMachineManager$Network$2012$VNic source
LEFT JOIN dbo.VirtualNicDimvw sourceDim
ON sourceDim.BaseManagedEntityId = source.BaseManagedEntityId
AND sourceDim.SourceId = source.DatasourceId
WHERE source.DWTimestamp >= @source1WM
AND source.DWTimestamp < @source1MaxWM
AND sourceDim.BaseManagedEntityId IS NULL
SELECT @rejectedRowCount = @@ROWCOUNT
BEGIN TRANSACTION
SELECT @task = 'Step 5: Inserting into VirtualNicSnapshotFact'
INSERT INTO dbo.VirtualNicSnapshotFactvw (
VirtualNicDimKey,
PaddingMeasure,
IPv4AddressType,
IPv6AddressType,
LastModifiedDate,
DateKey,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
source2.VirtualNicDimKey,
0 AS PaddingMeasure,
source2.IPv4AddressType,
source2.IPv6AddressType,
source2.[LastModified],
CONVERT(nvarchar(8), source2.[LastModified], 112) AS DateKey,
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformVirtualNicSnapshotFactProc_Source2 source2
WHERE source2.[LastModified] IS NOT NULL