LoadId

I thought I would explain the load id mechanism I have been using in my current project. I have stored couple of tables within "Stage" database. Decision was made to put it in the Stage database, against Audit database as these are not auditing tables.

The structure of the tables as follows:

CREATE TABLE dbo.Ctl_Load_Summary
(
LoadId int NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime NULL,
Duration int NOT NULL CONSTRAINT DF_Ctl_Load_Summary_Duration DEFAULT ((0))
)
GO

CREATE TABLE dbo.Ctl_Load_Batch_State
(
LoadId int NOT NULL,
InProgress varchar(1) NOT NULL
)
GO

"Ctl_Load_Batch_State" table always hold single record, which is controlled by a stored procedure (Code is below). This table will hold last successful load id OR current load id which is in progress. If it is in progress it will have the current load id and ‘Y’ as Progress flag. If the load is successful it will have the load id and ‘N’ as progress flag. During any unforeseen problems if the SSIS package fails during data warehouse load progress flag will remain as ‘Y’. When we rerun the package it will pick the same load id as the transaction within the package would have rolled the data back.


“Ctl_Load_Summary” table holds the summary details of the load. For example when we load data everyday it will have the loaded we have used for the day along with when the package started and ended. The duration of the package is calculated in seconds and stored in the duration column. This is mainly done to meet the client’s requirement.

Commets welcome.

Thanks
Sutha


Stored Procedure To Retrieve Load ID

-- =============================================
-- Author : Sutha Thiru
-- Create date : 12/12/2005
-- Description : This SP will return the latest .
-- =============================================
ALTER PROCEDURE [dbo].[prc_get_loadid] (@loadid INT OUTPUT)
AS

SET NOCOUNT ON

DECLARE @inprogress VARCHAR(1)
DECLARE @statecount INT
DECLARE @summarycount INT

SET @loadid = 0
SET @summarycount = 0
SET @statecount = 0

BEGIN TRY

BEGIN TRAN
SELECT @summarycount = count(*)
FROM Ctl_Load_Summary

SELECT @statecount = count(*)
FROM Ctl_Load_Batch_State

IF @summarycount <> 0 and @statecount = 0
BEGIN
RETURN 0
END
ELSE
SELECT @loadid = loadid,
@inprogress = inprogress
FROM Ctl_Load_Batch_State

IF @inprogress = 'Y'
BEGIN
set @loadid = @loadid
END
ELSE
SET @loadid = @loadid+1
IF @statecount = 0
BEGIN
INSERT
INTO Ctl_Load_Batch_State
VALUES (@loadid,'Y')
IF @@ERROR = 0
BEGIN
set @loadid = @loadid
END
END
ELSE
BEGIN
UPDATE Ctl_Load_Batch_State
SET loadid = @loadid,
inprogress ='Y'
IF @@ERROR = 0
BEGIN
set @loadid = @loadid
END
END
COMMIT TRAN

RETURN @loadid
END TRY

BEGIN CATCH
ROLLBACK TRAN
--EXEC Audit.dbo.prc_LogErrorInfo -- This calls another SP for auditing
END CATCH