Stored Procedure - usp_ETLExecutionLog

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

Name

Data Type

Direction

Description

Name	Type
ETLExecutionLog	Table

Name	Type
ETLExecutionLog	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_ETLExecutionLog_INSERT] Script Date: 03/09/2017 17:21:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            Craig Love
Date:            11/18/2013
Company:        PragmaticWorks / HETGroup
Project:        EnterpriseDW
Description:
    Inserts a record into the [dbo].[ETLExecutionLog] table. This
    table is used as a wrapper for other custom logged items.
*/
CREATE PROCEDURE [dbo].[usp_ETLExecutionLog_INSERT]
AS
SET NOCOUNT ON
BEGIN TRAN
BEGIN TRY
    INSERT INTO dbo.ETLExecutionLog
        (
          StartDtTm
         ,EndDtTm
         ,ExecutionStatus
        )
        VALUES
        (
        getdate()  --StartDtTm
        ,NULL  --EndDtTm
        ,'InProcess' -- ExecutionStatus - VARCHAR(10) NOT NULL
        );
        SELECT cast(scope_identity() AS INT) AS ETLExecutionLogId;
    COMMIT TRAN;
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;