如何从SSIS控制流持续检查表中作业状态并触发对应批处理？

Alright, let's work through how to implement this continuous polling and trigger system for your 20 SSIS batch flows. I’ve built similar monitoring setups before, so here’s a practical, scalable approach that should fit your needs:

The key here is to create a centralized polling control flow that monitors your status table, identifies which batch process needs to run, and triggers the corresponding SSIS package—all while avoiding redundant executions and overloading your database.

First, make sure your status table has the right columns to track each batch process uniquely and avoid duplicate triggers:

• Unique Process Identifier: Add a BatchProcessID (INT) or ProcessName (VARCHAR) column to distinguish your 20 flows.
• Status Field: A Status VARCHAR column to hold values like success.
• Process Tracking: Add an IsProcessed BIT column (default 0) or LastTriggeredDate DATETIME to mark entries that have already triggered their batch.
• Timestamp: A LastStatusUpdate DATETIME column to track when the status changed (helps with debugging).

Example table schema:

CREATE TABLE dbo.BatchStatus (
    BatchStatusID INT IDENTITY(1,1) PRIMARY KEY,
    BatchProcessID INT FOREIGN KEY REFERENCES dbo.BatchProcessConfig(BatchProcessID),
    Status VARCHAR(20) NOT NULL,
    LastStatusUpdate DATETIME DEFAULT GETDATE(),
    IsProcessed BIT DEFAULT 0
)

Create a dedicated SSIS package to handle the polling logic. Here’s the breakdown of its components:

• Indefinite Loop: Use a While Loop Container with a condition that always evaluates to True (you can add a manual stop condition later if needed, like checking for a "shutdown" flag in a table).
• Check for Trigger Statuses: Add an Execute SQL Task inside the loop to query the status table for unprocessed entries matching your target statuses. Example query:

  SELECT bp.BatchProcessID, bp.PackagePath
  FROM dbo.BatchStatus bs
  JOIN dbo.BatchProcessConfig bp ON bs.BatchProcessID = bp.BatchProcessID
  WHERE bs.Status = bp.TriggerStatus AND bs.IsProcessed = 0
  

  Store the results in an SSIS object variable for iteration.
• Iterate & Trigger Packages: Use a Foreach Loop Container (configured to iterate over the object variable) to launch each relevant batch package. Inside the loop:
  1. Map the BatchProcessID and PackagePath to SSIS variables.
  2. Use an Execute Package Task with an expression for the PackageLocation property, pointing to the path from your variable.
  3. Add an Execute SQL Task to mark the status entry as processed:

     UPDATE dbo.BatchStatus
     SET IsProcessed = 1
     WHERE BatchStatusID = ?
     

• Add a Delay: Insert a Script Task or an Execute SQL Task with WAITFOR DELAY '00:00:30' to pause the loop for 30 seconds (adjust based on how quickly you need to detect status changes). This prevents excessive database queries.

To keep things scalable (in case you add more flows later), create a centralized config table that maps each batch process to its trigger status and package location:

CREATE TABLE dbo.BatchProcessConfig (
    BatchProcessID INT PRIMARY KEY,
    ProcessName VARCHAR(50) UNIQUE NOT NULL,
    TriggerStatus VARCHAR(20) NOT NULL,
    PackagePath VARCHAR(255) NOT NULL,
    IsActive BIT DEFAULT 1
)

Populate this table with your 20 batch flows—this way, your polling package doesn’t need hardcoded logic for each process; it just reads from the config.

• Error Handling: Add OnError event handlers to your loop and package tasks to log failures to an error table (use SSIS logging or custom SQL inserts). You can also add retry logic for failed package launches.
• Avoid Infinite Loop Stuck: If you need the polling to stop once all relevant statuses are processed, add a condition to the While Loop that checks if there are any unprocessed entries left (use an Execute SQL Task to count them and store the result in a variable).
• Resource Efficiency: If sub-minute latency isn’t critical, schedule the polling package to run every 5-10 minutes via SQL Server Agent instead of running it continuously. This reduces resource usage on your SSIS server.
• Locking: Add UPDLOCK hints to your status table query to prevent race conditions where two polling instances pick up the same status entry.

内容的提问来源于stack exchange，提问作者Venkat