Stored Procedure - usp_ETLExecutionLog

Property	Value
Name	usp_ETLExecutionLog_DetailReport
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name	Data Type	Direction	Description
@ETLExecutionLogId	int(10, 0)	Input
@ExecutionStartDate	datetime(23, 3)	Input
@ExecutionEndDate	datetime(23, 3)	Input

Name	Type
ETLExecutionLog	Table
ETLExecutionLog_SSISConfigurations	Table
usp_ETLExecutionLog_MostRecentExecution	StoredProcedure

Name	Type
usp_ETLExecutionLog_MostRecentExecution	STOREDPROCEDURE
ETLExecutionLog_SSISConfigurations	TABLE
ETLExecutionLog	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_ETLExecutionLog_DetailReport] 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:
    Provides information contained in the ETL Execution Logs.
    This procedure provides information for the data contained in the
    ETL execution logs. The following paramters may be used with the procedure:

    @ETLExecutionLogId - Unique ideniftying integer value for the logs associated with a single ETL execution.
    @ExecutionStartDate - Start of range of date/time value for logged executions. Must be a valid datetime format-
    @ExecutionEndDate - End of range of date/time value for logged executions.
    Sample:
    1 - Returns logged information for the log with the id of 123
    EXEC [dbo].[usp_ETLExecutionLog_DetailReport]
        @ETLExecutionLogId = 123
    2 - Returns logged information for the date and time range 9/1/2013 6:00AM through 9/3/2013 7:00 PM
    EXEC [dbo].[usp_ETLExecutionLog_DetailReport]
        @ExecutionStartDate = '9/1/2013 6:00AM',
        @ExecutionEndDate = '9/3/2013 7:00PM'

*/
CREATE PROCEDURE [dbo].[usp_ETLExecutionLog_DetailReport]
    @ETLExecutionLogIdint = NULL,
@ExecutionStartDate datetime = NULL,
@ExecutionEndDate datetime = NULL
AS
SET NOCOUNT ON
IF (@ETLExecutionLogId IS NULL AND @ExecutionStartDate IS NULL and @ExecutionEndDate IS NULL)
BEGIN
    PRINT 'This procedure provides information for the data contained in the
ETL execution logs. The following paramters may be used with the procedure:

@ETLExecutionLogId - Unique ideniftying integer value for the logs associated with a single ETL execution.
@ExecutionStartDate - Start of range of date/time value for logged executions. Must be a valid datetime format-
@ExecutionEndDate - End of range of date/time value for logged executions.
Sample:
1 - Returns logged information for the log with the id of 123
EXEC [dbo].[usp_ETLExecutionLog_DetailReport]
    @ETLExecutionLogId = 123
2 - Returns logged information for the date and time range 9/1/2013 6:00AM through 9/3/2013 7:00 PM
EXEC [dbo].[usp_ETLExecutionLog_DetailReport]
    @ExecutionStartDate = ''9/1/2013 6:00AM'',
    @ExecutionEndDate = ''9/3/2013 7:00PM''

    ';

    RETURN;
END
DECLARE @ETLExecutionLogId_StartRange int = 0
DECLARE @ETLExecutionLogId_EndRange int = 2147483647
IF @ETLExecutionLogId IS NOT NULL
BEGIN
    SET @ETLExecutionLogId_StartRange = @ETLExecutionLogId
    SET @ETLExecutionLogId_EndRange = @ETLExecutionLogId
END;
IF @ExecutionStartDate IS NULL AND
    (@ExecutionEndDate IS NOT NULL OR @ETLExecutionLogId IS NOT NULL)
BEGIN
    SET @ExecutionStartDate = '1/1/1900'
END;
IF @ExecutionEndDate IS NULL AND
    (@ExecutionStartDate IS NOT NULL OR @ETLExecutionLogId IS NOT NULL)
BEGIN
    SET @ExecutionEndDate = '12/31/2999'
END;
/*
-- Debug items
PRINT @ETLExecutionLogId
PRINT @ETLExecutionLogId_StartRange
PRINT @ETLExecutionLogId_EndRange
PRINT @ExecutionStartDate
PRINT @ExecutionEndDate
*/
PRINT 'Parameters:
@ETLExecutionLogId = ' + ISNULL(CAST(@ETLExecutionLogId AS varchar(20)), 'N/A')
+  '
@ExecutionStartDate = ''' +
    CASE
        WHEN @ExecutionStartDate = '1/1/1900' THEN 'No Date Entered'
        ELSE CONVERT(varchar(40), @ExecutionStartDate, 109)
    END
    + ''''
+  '
@ExecutionEndDate = ''' +
    CASE
        WHEN @ExecutionEndDate = '12/31/2999' THEN 'No Date Entered'
        ELSE CONVERT(varchar(40), @ExecutionEndDate, 109)
    END
    + ''''
DECLARE @Current_ETLExecutionLogId int
DECLARE curLogs CURSOR FAST_FORWARD
    FOR
        SELECT ETLExecutionLogId
        FROM dbo.ETLExecutionLog el
            WHERE(el.ETLExecutionLogId >= @ETLExecutionLogId_StartRange AND el.ETLExecutionLogId <= @ETLExecutionLogId_EndRange)
            AND (el.StartDtTm >= @ExecutionStartDate)
            AND (ISNULL(el.EndDtTm, GETDATE()) <= @ExecutionEndDate);
OPEN curLogs
FETCH NEXT FROM curLogs INTO @Current_ETLExecutionLogId
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ''
    PRINT '============================================================'
    PRINT 'ETLExecutionLog'
    PRINT '============================================================'
    PRINT ''
    SELECT *
    FROM dbo.ETLExecutionLog el
        WHEREel.ETLExecutionLogId = @Current_ETLExecutionLogId;
    PRINT '============================================================'
    PRINT 'ETLExecutionLog_SSISConfigurations'
    PRINT '============================================================'
    PRINT ''
    SELECT
        elsc.SSISConfigurations_Log_Id,
        elsc.ETLExecutionLogId,
        elsc.ETL_LogPoint,
        elsc.InsertDtTm,
        CAST(elsc.ConfigurationFilter As varchar(40)) AS ConfigurationFilter,
        CAST(elsc.ConfiguredValue As varchar(150)) AS ConfiguredValue,
        CAST(elsc.PackagePath As varchar(150)) AS PackagePath,
        CAST(elsc.ConfiguredValueType As varchar(15)) AS ConfiguredValueType
    FROM dbo.ETLExecutionLog el
    INNER JOIN dbo.ETLExecutionLog_SSISConfigurations elsc
        ON (elsc.ETLExecutionLogId = el.ETLExecutionLogId)
        WHEREel.ETLExecutionLogId = @Current_ETLExecutionLogId;
    PRINT '**************************************************************************************'
    FETCH NEXT FROM curLogs INTO @Current_ETLExecutionLogId;
END;
PRINT ''
PRINT 'End of Report'
CLOSE curLogs;
DEALLOCATE curLogs;