Schedule an SSIS package with dependency

Question

Hi, I am trying to execute an SSIS package in SQL agent. Requirement is to check for a record in the log table and only if it exists then I should execute the SSIS package. For business reasons I cannot incorporate this logic into the SSIS package.

I need to implement this logic as a Step1 and Step2 in the SQL agent job.

Step1: Check if the record exists in the log table. Go to step2 only if a record exists.

Step2: Execute the SSIS package.

I know how to schedule an SSIS package in an agent job but need help with the logic above. THanks in advance for the suggestions.

Answer 1

The only way to do it as described is to raise an an error if the log record isn't there, and the set up the job step with "Quit on failure". But this also means that the job will appear as failed, when the log record is missing. This is not going to be popular.

Better alternative is to have two jobs, for for checking the log file, and one for the SSIS package. Only the first job is scheduled. If the log record is there, this job step runs sp_start_job to start the other job.

Answer 2

You can create an extra SQL agent job with the step to check if the record exists in the log table. If yes, fire the SSIS package job.

IF EXISTS (SELECT 1 FROM LogTable WHERE ...) 
BEGIN
    EXEC msdb.dbo.sp_start_job N'SSISPackageJob';
END 

You only need to set a scheduler on the first job.