IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = N'TransformVirtualDiskDriveSnapshotFactProc')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[TransformVirtualDiskDriveSnapshotFactProc] @WaterMark xml AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.TransformVirtualDiskDriveSnapshotFactProc (@WaterMark xml)
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
IF OBJECT_ID('DWTemp.TransformVirtualDiskDriveSnapshotFactProc_Source2') IS NOT NULL
DROP TABLE DWTemp.TransformVirtualDiskDriveSnapshotFactProc_Source2
-- '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.TransformVirtualDiskDriveSnapshotFactProc_Source1 source
INNER JOIN dbo.VirtualDiskDriveDimvw d
ON source.BaseManagedEntityId = d.BaseManagedEntityId
INNER JOIN dbo.VirtualDiskDriveSnapshotFactvw SnapShotFact
ON source.LastModified = SnapShotFact.LastModifiedDate
AND d.VirtualDiskDriveDimKey = SnapShotFact.VirtualDiskDriveDimKey
-- 'Step 3: Copying Records into a temp Table'
SELECT @task = 'Copying Records into a temp Table'
SELECT
d.VirtualDiskDriveDimKey,
source1.[LastModified],
source1.[Microsoft.SystemCenter.VirtualMachineManager.VirtualDiskDrive!DriveType] AS DriveType
INTO DWTemp.TransformVirtualDiskDriveSnapshotFactProc_Source2
FROM DWTemp.TransformVirtualDiskDriveSnapshotFactProc_Source1 source1
INNER JOIN dbo.VirtualDiskDriveDimvw 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$2012$VirtualDiskDrive source
LEFT JOIN dbo.VirtualDiskDriveDimvw 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 VirtualDiskDriveSnapshotFact'
INSERT INTO dbo.VirtualDiskDriveSnapshotFactvw (
VirtualDiskDriveDimKey,
PaddingMeasure,
DriveType,
LastModifiedDate,
DateKey,
InsertedBatchId,
UpdatedBatchId
)
SELECT DISTINCT
source2.VirtualDiskDriveDimKey,
0 AS PaddingMeasure,
source2.DriveType,
source2.[LastModified],
CONVERT(nvarchar(8), source2.[LastModified], 112) AS DateKey,
@BatchId AS InsertedBatchId,
0 AS UpdatedBatchId
FROM DWTemp.TransformVirtualDiskDriveSnapshotFactProc_Source2 source2
WHERE source2.[LastModified] IS NOT NULL
IF OBJECT_ID('DWTemp.TransformVirtualDiskDriveSnapshotFactProc_Source2') IS NOT NULL
DROP TABLE DWTemp.TransformVirtualDiskDriveSnapshotFactProc_Source2
END TRY
BEGIN CATCH
DECLARE @errorFmt VARCHAR(256)