--© 2018 | ByrdNest Consulting

IF OBJECT_ID(N'etl.csp_ETLAdmin') IS NOT NULL DROP PROCEDURE etl.csp_ETLAdmin
GO
CREATE PROCEDURE etl.csp_ETLAdmin
AS

	DECLARE @DATE					DATETIME = GETUTCDATE();
	DECLARE @Error					INT = 0;
	DECLARE @Restart				Char(1) = 'N';
	DECLARE @ETLIDold				INT;
	DECLARE @p_nETLID				INT;
	DECLARE @PreviousChangeVersion	BIGINT;
	DECLARE @CurrentChangeVersion	BIGINT;
	DECLARE @ETLWaitTime			INT = 10;		--Time increment between ETL runs
	DECLARE @StartDate				DATETIME;
	DECLARE @EndDate				DATETIME;
	DECLARE @SQL					NVARCHAR(4000);
	DECLARE @Parm					NVARCHAR(4000);
	DECLARE @Flag					CHAR(1);
	DECLARE @ErrorMessage	NVARCHAR(4000);
	DECLARE @ErrorSeverity	INT;
	DECLARE @ErrorState		INT;
	DECLARE @ObjectName		NVARCHAR(1000);

	SET NOCOUNT ON;

	BEGIN TRY

	--Check status of ETL
	SET @ETLIDold = (SELECT TOP 1 ETLID FROM etl.[ETL] WHERE EndDate IS NULL ORDER BY ETLID DESC)
	IF @ETLIDold IS NOT NULL		--SQL error from previous run(s) or ETL still running
		BEGIN
			SET @StartDate = (SELECT StartDate FROM etl.[ETL] where ETLID = @ETLIDold)
			IF DATEADD(mm,@ETLWaitTime,@StartDate) > @DATE
				RETURN 0		--ETL still running
			ELSE
				SET @Restart = 'Y'
		END


	SELECT @PreviousChangeVersion = MAX(CurrentChangeVersion)
		FROM etl.ETL 
		WHERE CTCompleteFlag = 'Y'
	SET @PreviousChangeVersion = ISNULL(@PreviousChangeVersion,0);

	--since change tracking in implemented in AdventureWorks2012 DB and this sproc is in AdventureWorks2012DW, have to run dynamic sql to create batch to run in AdventureWorks2012 DB.
	SET @SQL = N'USE AdventureWorks2012;
				SET @CurrentChangeVersion =CHANGE_TRACKING_CURRENT_VERSION();'
	SET @Parm = N'@CurrentChangeVersion BIGINT OUTPUT'
	EXEC sp_executesql @SQL, @Parm, @CurrentChangeVersion = @CurrentChangeVersion OUTPUT;
	--SELECT @PreviousChangeVersion;
	--SELECT @CurrentChangeVersion;

   IF @CurrentChangeVersion > @PreviousChangeVersion
		BEGIN
			INSERT etl.ETL
			    (StartDate, CTCompleteFlag, ExtractCompleteFlag, LoadCompleteFlag, PreviousChangeVersion, CurrentChangeVersion) -- Set Change Versions
				VALUES (@DATE, 'N', 'N', 'N', @PreviousChangeVersion, @CurrentChangeVersion)
		
			SET @p_nETLID = SCOPE_IDENTITY();
		END
	ELSE
		RETURN 0;			--Return success if no data modifications since last runs

	IF @Restart = 'N'
		BEGIN
			DELETE etl.ETLSourceRecord
			DELETE etl.InternetSalesFactExtract
			DELETE etl.CustomerDimExtract
			DELETE etl.ProductDimExtract
		END

	--get changed records
	EXEC etl.csp_GetChangedRecords @p_nETLID,  @PreviousChangeVersion, @CurrentChangeVersion;

	-- do extracts/transforms as needed
	EXEC etl.csp_CustomerDimExtract @p_nETLID,  @PreviousChangeVersion, @CurrentChangeVersion;

	EXEC etl.csp_ProductDimExtract @p_nETLID,  @PreviousChangeVersion, @CurrentChangeVersion;

	EXEC etl.csp_InternetSalesFactExtract @p_nETLID,  @PreviousChangeVersion, @CurrentChangeVersion;


	UPDATE etl.[ETL]
		SET ExtractCompleteFlag = 'Y'
		WHERE ETLID = @p_nETLID

	--now that extract is complete, truncate etl.ETLSourceRecord
	DELETE etl.ETLSourceRecord

	--do transforms.loads as needed

	EXEC etl.csp_CustomerDimLoad;

	EXEC etl.csp_ProductDimLoad;

	EXEC etl.csp_InternetSalesFactLoad;
	
	SET @EndDate = GETUTCDATE();
	UPDATE etl.[ETL]
		SET LoadCompleteFlag = 'Y',
			EndDate = @EndDate
		WHERE ETLID = @p_nETLID

	--now that Loads are complete, can cleanup extract tables
--	DELETE etl.ProductDimExtract
--	DELETE etl.CustomerDimExtract
--	DELETE etl.InternetSalesFactExtract

	--in case of a restart and successful ETL, make sure all ETL records have EndDate

		UPDATE etl.[ETL]
		SET EndDate = @EndDate
		WHERE EndDate IS NULL

	RETURN 0;
	
END TRY

BEGIN CATCH
		SET @Error = @@ERROR
		SELECT @ObjectName		= Object_Name(@@ProcID);
        SELECT @ErrorMessage	= ERROR_MESSAGE();
        SELECT @ErrorSeverity	= ERROR_SEVERITY();
        SELECT @ErrorState		= ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
	RETURN @Error;
END CATCH
GO
EXEC etl.csp_ETLAdmin
GO