question

CzarR-3851 avatar image
0 Votes"
CzarR-3851 asked ErlandSommarskog answered

Schedule an SSIS package with dependency

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.

sql-server-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.