DWOperations
 usp_ETLExecutionLog_ResetIncrementalLoadDates (Stored Procedure)
Home  Diagrams  Documents  Images  DWOperations  Storage  Users  Roles  Schemas  Tables  Views  Indexes  Triggers  Stored Procedures  Functions  Data Types  XML Schemas  Rules  Defaults  Fulltext Catalogs  Synonyms  Keys  Partitions  Assemblies 
Current Path:  DWOperations   Stored Procedures   usp_ETLExecutionLog_ResetIncrementalLoadDates
  Properties
Property Value
Name usp_ETLExecutionLog_ResetIncrementalLoadDates
Schema dbo
Is Encrypted False
Ansi Nulls Status True
Quoted Identifier Status True
Description
  Parameters
Name Data Type Direction Description
int(10, 0)
Input
varchar(10)
Input
  Parent Dependencies (objects that usp_ETLExecutionLog_ResetIncrementalLoadDates depends on)
Name Type
Table
Table
Table
  Child Dependencies (objects that depend on usp_ETLExecutionLog_ResetIncrementalLoadDates)
Name Type
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: StoredProcedure [dbo].[usp_ETLExecutionLog_ResetIncrementalLoadDates] Script Date: 03/09/2017 17:21:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            Craig Love
Date:            11/21/2013
Company:        PragmaticWorks / HETGroup
Project:        EnterpriseDW
Description:    
    This procedure can be used to set the datetime values in the
    [SSIS Configurations] table for the Var_IncrementalStartDtTm and Var_IncrementalEndDtTm
    configuration records to those used during a previous ETL execution. The
    values are retrieved from the [ETLExecutionLog_SSISConfigurations] table.
    That table logs the values stored in the [SSIS Configurations] table for previous
    ETL executions.
    The following paramters may be used with the procedure:
        
    @ETLExecutionLogId - Unique ideniftying integer value for the logs associated with a single ETL execution.
    @Mode -
            ''Preview'' - Procedure will display the BEFORE and AFTER of the Var_IncrementalStartDtTm and Var_IncrementalEndDtTm
                         values stored in the [SSIS Configurations] table. This is the default value. If this option is
                         used, a value should also be passed into the @ETLExecutionLogId parameter.
            ''Reset'' - Procedure will display the Var_IncrementalStartDtTm and Var_IncrementalEndDtTm
                         values currently stored in the [SSIS Configurations] table. If this option is used, a value should
                         also be passed into the @ETLExecutionLogId parameter.
2015-03-09 - Sophia Cowan - Updated Stored Procedure to change DWStaging back to Original settings, not Snapshot Name                        
*/
CREATE PROCEDURE [dbo].[usp_ETLExecutionLog_ResetIncrementalLoadDates]
    @ETLExecutionLogIdint = NULL,
@Mode varchar(10) = 'Preview'
AS
SET NOCOUNT ON;
IF (@ETLExecutionLogId IS NULL)
BEGIN
    PRINT 'This procedure can be used to set the datetime values in the
[SSIS Configurations] table for the Var_IncrementalStartDtTm and Var_IncrementalEndDtTm
configuration records to those used during a previous ETL execution. The
values are retrieved from the [ETLExecutionLog_SSISConfigurations] table.
That table logs the values stored in the [SSIS Configurations] table for previous
ETL executions.
The following paramters may be used with the procedure:
    
@ETLExecutionLogId - Unique ideniftying integer value for the logs associated with a single ETL execution.
@Mode -
        ''Preview'' - Procedure will display the BEFORE and AFTER of the Var_IncrementalStartDtTm and Var_IncrementalEndDtTm
                     values stored in the [SSIS Configurations] table. This is the default value. If this option is
                     used, a value should also be passed into the @ETLExecutionLogId parameter.
        ''Reset'' - Procedure will display the Var_IncrementalStartDtTm and Var_IncrementalEndDtTm
                     values currently stored in the [SSIS Configurations] table. If this option is used, a value should
                     also be passed into the @ETLExecutionLogId parameter.
    ';
    
    RETURN;
END;
/*
BEGIN - Parameter Validity Checks
*/
IF @Mode != 'Preview' AND @Mode != 'Reset' 
BEGIN
    PRINT 'The parameter @Mode only accepts the values ''Preview'' or ''Reset''. Execution stopped.';
    RETURN;
END;
IF NOT EXISTS(SELECT el.ETLExecutionLogId FROM dbo.ETLExecutionLog el WHERE el.ETLExecutionLogId = @ETLExecutionLogId)
BEGIN
    PRINT 'No record exists in the dbo.ETLExecutionLog table where ETLExecutionLogId = ' + CAST(@ETLExecutionLogId AS varchar(20)) + '. Execution stopped.' ;
    RETURN;
END;
/*
END - Parameter Validity Checks
*/
PRINT 'Parameters:
@ETLExecutionLogId = ' + ISNULL(CAST(@ETLExecutionLogId AS varchar(20)), 'N/A')
+  '
@Mode = ''' + @Mode + '''';
DECLARE @CurrentBeginDtTm varchar(40);
DECLARE @CurrentEndDtTm varchar(40);
DECLARE @CurrentStaging varchar(40);
DECLARE @TargetBeginDtTm varchar(40);
DECLARE @TargetEndDtTm varchar(40);
DECLARE @TargetStaging varchar(40);
DECLARE @CurrentSettings varchar(300);
DECLARE @TargetSettings varchar(300);
SELECT @CurrentBeginDtTm = sc.ConfiguredValue
FROM dbo.[SSIS Configurations] sc
WHERE sc.ConfigurationFilter = 'Var_IncrementalBeginDtTm';
SELECT @CurrentEndDtTm = sc.ConfiguredValue
FROM dbo.[SSIS Configurations] sc
WHERE sc.ConfigurationFilter = 'Var_IncrementalEndDtTm';
SELECT @CurrentStaging = sc.ConfiguredValue
FROM dbo.[SSIS Configurations] sc
WHERE sc.ConfigurationFilter = 'ConnMgr_DWStaging'
AND sc.PackagePath = '\Package.Connections[DWStaging].Properties[InitialCatalog]'
SET @CurrentSettings = 'Current [dbo].[SSIS Configuration] values:
Var_IncrementalBeginDtTm = ''' + @CurrentBeginDtTm + '''' + '
Var_IncrementalEndDtTm = ''' + @CurrentEndDtTm + '''' + '
ConnMgr_DWStaging = ''' + @CurrentStaging + '''';
SELECT 
    @TargetBeginDtTm = elsc.ConfiguredValue
FROM dbo.ETLExecutionLog el
INNER JOIN dbo.ETLExecutionLog_SSISConfigurations elsc
    ON (elsc.ETLExecutionLogId = el.ETLExecutionLogId)
    WHEREel.ETLExecutionLogId = @ETLExecutionLogId
    AND elsc.ConfigurationFilter = 'Var_IncrementalBeginDtTm'
    AND elsc.ETL_LogPoint = 'Initial State';
SELECT 
    @TargetEndDtTm = elsc.ConfiguredValue
FROM dbo.ETLExecutionLog el
INNER JOIN dbo.ETLExecutionLog_SSISConfigurations elsc
    ON (elsc.ETLExecutionLogId = el.ETLExecutionLogId)
    WHEREel.ETLExecutionLogId = @ETLExecutionLogId
    AND elsc.ConfigurationFilter = 'Var_IncrementalEndDtTm'
    AND elsc.ETL_LogPoint = 'Initial State';
SELECT @TargetStaging = sc.ConfiguredValue
FROM dbo.[SSIS Configurations] sc
WHERE sc.ConfigurationFilter = 'Var_DWStagingDBName'
SET @TargetSettings = 'Targeted [dbo].[SSIS Configuration] values:
Var_IncrementalBeginDtTm = ''' + @TargetBeginDtTm + '''' + '
Var_IncrementalEndDtTm = ''' + @TargetEndDtTm + '''' + '
ConnMgr_DWStaging = ''' + @TargetStaging + '''';
IF @Mode = 'Preview'
BEGIN
    PRINT '
PREVIEW MODE - No changes to [dbo].[SSIS Configuration] have been made.
' + @CurrentSettings + '
' + @TargetSettings;
    RETURN;
END;
IF @Mode = 'Reset'
BEGIN
    BEGIN TRAN;
    PRINT '';
    PRINT 'RESET MODE - The following changes will be made to [dbo].[SSIS Configuration].
' + @CurrentSettings + '
' + @TargetSettings;
    PRINT ''
    BEGIN TRY
        UPDATE dbo.[SSIS Configurations]
        SET ConfiguredValue = @TargetBeginDtTm
        WHERE ConfigurationFilter = 'Var_IncrementalBeginDtTm';
        UPDATE dbo.[SSIS Configurations]
        SET ConfiguredValue = @TargetEndDtTm
        WHERE ConfigurationFilter = 'Var_IncrementalEndDtTm';
        
        UPDATE dbo.[SSIS Configurations]
        SET ConfiguredValue = @TargetStaging
        WHERE ConfigurationFilter = 'ConnMgr_DWStaging'
        AND PackagePath = '\Package.Connections[DWStaging].Properties[InitialCatalog]';
    
        UPDATE dbo.[ETLExecutionLog]
        SET ExecutionStatus = 'Reset'
        WHERE ETLExecutionLogId = @ETLExecutionLogId;
        COMMIT TRAN;
        PRINT 'Var_IncrementalBeginDtTm, Var_IncrementalEndDtTm and ConnMgr_DWStaging values have been changed.
Current records in [dbo].[SSIS Configuration] are:
'
        SELECT 
            CAST(sc.ConfigurationFilter As varchar(40)) AS ConfigurationFilter, 
            CAST(sc.ConfiguredValue As varchar(40)) AS ConfiguredValue, 
            CAST(sc.PackagePath As varchar(70)) AS PackagePath, 
            CAST(sc.ConfiguredValueType As varchar(15)) AS ConfiguredValueType
        FROM dbo.[SSIS Configurations] sc
        WHERE sc.ConfigurationFilter IN ('Var_IncrementalBeginDtTm','Var_IncrementalEndDtTm','ConnMgr_DWStaging')
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN;
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;
        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
        -- Use RAISERROR inside the CATCH block to return error
        -- information about the original error that caused
        -- execution to jump to the CATCH block.
        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
            
    END CATCH;
END;
Powered by BI Documenter