CODEWITHSUNDEEP
sql-serverssis
Venkat
Venkat

Reputation: 3

ssis-How do I check job status in a table continuously from SSIS control flow?

we have a requirement where SSIS job should trigger based on the availability of value in the status table maintained,point to remember here that we are not sure about the exact time when the status is going to be available so my SSIS process must continuously look for the value in status table,if value(ex: success) is available in status table then job should trigger.here we have 20 different ssis batch processes which should invoke based on respective/related status value is available.

Upvotes: 0

Views: 1156

Answers (4)

J Weezy
J Weezy

Reputation: 3935

I would take @Long's approach, but enhance it by doing the following:

1.) use Execute SQL Task to query the status table for all records that pertain to the specific job function and load the results into a recordset. Note: the variable that you are loading the recordset into must be of type object.

2.) Create a Foreach Loop enumerator of type ADO to loop over the recordset.

3.) Do stuff.

4.) When the job is complete, go back to the status table and mark the record complete so that it is not processed again.

5.) Set the job to run periodically (e.g., minute, hourly, daily, etc.).

The enhancement hear is that no flags are needed to govern the job. If a record exists then the foreach loop does its job. If no records exist within the recordset then the job exits successfully. This simplifies the design.

Upvotes: 0

KeithL
KeithL

Reputation: 5594

You mentioned the word trigger. How about you create a trigger when that status column meets the criteria to run the packages:

Also this is how to run a package from T-SQL: https://www.timmitchell.net/post/2016/11/28/a-better-way-to-execute-ssis-packages-with-t-sql/

You might want to consider creating a master package that runs all the packages associated with this trigger.

Upvotes: 0

Ferdipux
Ferdipux

Reputation: 5246

Starting a SSIS package takes some time. So I would recommend to create a package with the following structure:

  1. Package variable Check_run type int, initial value 1440 (to stop run after 24 hours if we run check every minute). This is to avoid infinite package run.
  2. Set For Loop, check if Check_run is greater than zero and decrement it on each loop run.
  3. In For loop check your flag variable in Exec SQL task, select single result value and assign its result to a variable, say, Flag.
  4. Create conditional execution branches based on Flag variable value. If Flag variable is set to run - start other packages. Otherwise - wait for a minute with Exec SQL command waitfor delay '01:00'

Upvotes: 0

LONG
LONG

Reputation: 4610

What you can do is:

  1. Scheduled the SSIS package that run frequently.
  2. For that scheduled package, assign the value from the table to a package variable
  3. Use either expression for disabling the task or constraint expression to let the package proceeds.

Upvotes: 1

Related Questions