Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?--All projects
-- =============================================
-- Description: Based on supplied batch date, if the data is safe for batch load
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE dbo.[uspSetBatchLogsReadyforBatch]
--@batchDate datetime2(7), **commenting out for testing since dates giving so much trouble**
@rawTableName varchar (50),
@batchLogId bigint OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
--Determine if there is a record in the SASLogs already.
/* RULES for period/behavior type:
If already exists with a 'INTPROCESSING', then create a brand new batchlogid
If already exists with a 'EXTPROCESSING' or 'EXTCOMPLETE', reuse that one and return as @batchLogId
IF doesn't exist yet, then insert a new record and return that identity in @batchLogId
**possibly add a badbatch field**
*/
--This code is only needed if you want to block multiple batches from existing.
--Leave this commented out unless really only want only one batch at a time while in processing
--IF (SELECT BatchLogID FROM BatchLogs
-- WHERE BatchDate = @batchDate **commenting out for testing since dates giving so much trouble**
-- AND TableName = @rawTableName
-- AND LoadStatus = 'INTPROCESSING') > 0
-- BEGIN
-- SET @batchLogId = 0
-- SELECT @batchLogId;
-- RETURN
-- END
/*
Take this section out. While it would be good to be frugal,
this won't conquer all situations accordingly. Here's the situation:
--Data flow job starts
--Requests a batchLogId (e.g., id = 3)
--raw data flows in, tagged with batchLogID = 3
--let's say that the authoritative source has an ETLBatchID <= 17 that we can use
--close out the batch as EXTCOMPLETE
--now, let's launch the data flow again, but the transform/load hasn't happened yet
--With code below, batchLogId = 3, but the ETLBatch would > 17 and <= 42,
--so the data set would be something different, and BOOM, we lost some data <= 17
--Trying with it in, but only if in EXTProcessing or EXTError states
SELECT @batchLogId = MIN(BatchLogID) --adding MIN here since we're testing to not look for date--
FROM BatchLogs
WHERE
--BatchDate = @batchDate **commenting out for testing since dates giving so much trouble**
--AND
TableName = @rawTableName
AND LoadStatus In('EXTPROCESSING','EXTERROR');
*/
IF @batchLogId = 0 OR @batchLogId Is Null
BEGIN
INSERT INTO BatchLogs (TableName, BatchDate, LoadStatus, LoadCompleteDate)
VALUES (@rawTableName, GETDATE() ,'EXTPROCESSING', GETDATE()); --replaced @batchDate with GETDATE()--
SET @batchLogId = @@IDENTITY;
SELECT @batchLogId;
END
ELSE
BEGIN
UPDATE BatchLogs SET
BatchDate = GETDATE(),
LoadCompleteDate = GETDATE(),
LoadStatus = 'EXTPROCESSING'
WHERE BatchLogID = @batchLogId;
SELECT @batchLogId;
END
END TRY
BEGIN CATCH
PRINT 'There was an error prepping the Batch log' + CHAR(13);
THROW;
END CATCH
END
GO