Run SSIS package runs when a table it full

Question

I have the following process:

1. An external process fills a staging table called: staging_table.
2. Something to trigger the SSIS package
3. The SSIS package does the business logic and truncates staging_table.
4. Then the process starts again.

The external process does not execute at a specific time, so creating job to run a specific time is not a solution. I want the package to execute when the table is filled. Is it possible to execute an SSIS package when a table is filled? If it is possible, then how would that work and what needs to be done? FYI, this verions of SQL Server is 2008 R2.

Answer 1

"I want the package to execute when the table is filled"

It is a bit vague, but you could create AFTER INSERT TRIGGER:

create trigger staging_table_trigger
on staging_table after insert as
begin
    Declare @execution_id bigint;
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
        @execution_id=@execution_id OUTPUT,
        @folder_name=N'Deployed Projects',
        @project_name=N'Integration Services Project1',
        @use32bitruntime=False,
        @reference_id=Null;

    --params if any
    DECLARE @var0 smallint = 1
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
       @object_type=50,
       @parameter_name=N'LOGGING_LEVEL',
       @parameter_value=@var0;

     -- run package
     EXEC [SSISDB].[catalog].[start_execution] @execution_id;
end

Run an SSIS package from SSMS with Transact-SQL

---

Please note that this mechanism is not suitable for highly concurent OLTP system, in such scenario you should consider asynchronous handling with SERVICE BROKER.

More info: Advanced Service Broker Sample: Asynchronous Triggers by Eitan Blumin

Answer 2

There would be a number of ways to accomplish this. This is one way that I've implemented a few times.

This is dependent on the table being truncated at the end of the SSIS package.

Add an Execute SQL Task to your SSIS package that performs a row count on your table. Return that count to an SSIS variable. Have the Precedent Constraint that comes off the Execute SQL Task check the value of the variable, and only proceed to the rest of the package logic if the count is greater than zero. Otherwise, stop there.

Then just schedule the job to run as often as makes sense. If the table gets populated every day, maybe run the job every few hours. If it gets populated every hour, maybe run the job every few minutes.

The overhead of counting records on an empty table is minimal, so excess runs won't negatively impact your environment.