SQL Server – How to Prevent Concurrency Issues in SSIS Package

concurrencysql serversql-server-2017ssis

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)

Related Question