Have a SQL Server 2017 Server.
Have several SQL Server jobs that call a single SSIS package using the SYNCHRONIZED flag.
In the package there is a data flow that runs a stored proc (please see below) that returns a Data Source that checks to see if there are any labels to be printed. If there are any labels to be printed then it selects those labels into a table variable, selects the labels, and updates the labels IsProcessed flag to say that they have been processed. The problem is that if more than one job calls the package then it prints out more than one label even thought there is only one label to be processed. This situation does not consistently occur. So, my question is: How do I prevent this from happening?
CREATE PROC [dbo].[spCreateOutput_LabelDataSet]
AS
BEGIN
BEGIN TRY
BEGIN TRAN
DECLARE @Intermediate TABLE(
PrintLabelsID INT NULL,
Labels XML NULL
)
INSERT INTO @Intermediate(PrintLabelsID, Labels)
SELECT
pl.PrintLabelsID,
pl.Label
FROM
Staging.dbo.PrintLabels pl
WHERE
pl.IsProcessed = 0
SELECT
CAST(Labels AS VARCHAR(MAX)) 'Labels'
FROM
@Intermediate i
UPDATE pl
SET IsProcessed = 1,
ProcessedDate = GETDATE()
FROM
Staging.dbo.PrintLabels pl
INNER JOIN @Intermediate i
ON i.PrintLabelsID = pl.PrintLabelsID
COMMIT TRAN
END TRY
-- Error Logging
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = LEFT(ERROR_MESSAGE(), 4000);
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
DECLARE @Error_Procedure VARCHAR(200) = LEFT(ERROR_PROCEDURE(), 200);
EXEC Logs.dbo.spTrackErrors @ErrorNumber, @ErrorSeverity, @ErrorState, @Error_Procedure, @ErrorLine, @ErrorMessage;
THROW;
END;
END CATCH
END
Best Answer
Since it seems that any concurrent execution of this package is going to cause this problem for you, I'd recommend putting controls into the package that prevent itself from running while another job is running it.
As far as I know, there is no mechanism within SSIS to set a package as 'one execute at a time only', so you would need to have a solution to this implemented in your SQL database. Create a table that records each instance of the package being executed (this is useful to do anyway for auditing/debugging purposes) and record the start/end times of package execution, and maybe a column for status. If there is a job still running, the end time will be null, so your package should check for this and quit if it exists.
The risk with this approach is that a fatal error might cause an unfinished row to remain in the table, blocking any further execution. You should put something into your package to update it under all error conditions, and perhaps some logic to finish a row for a job if it has gone on too long (figuring out this duration is a business question)