CODEWITHSUNDEEP
ssis
user2157914
user2157914

Reputation: 1

How to make a SSIS package to monitor the status of a column in a DB table and then execute the ssis package if the status=1?

How to make a SSIS package to monitor the status of a column in a DB table and then execute the ssis package if the status=1

Upvotes: 0

Views: 885

Answers (1)

user1896267
user1896267

Reputation:

You could set up the SSIS package in a SQL Agent job without a defined schedule. Then create a trigger on the status field of the table. When the status changes from 0 to 1, execute the job that will then run the SSIS package.

Your trigger could look something like the following. I have not checked the syntax and I recommend tweaking it in testing to verify that it launches appropriately.

CREATE TRIGGER TriggerName
ON TableName 
AFTER UPDATE
IF UPDATE (status) AND status = 1
BEGIN
  EXEC msdb..sp_start_job 
     @job_name = 'JobName';
GO
END

The following link illustrates different approaches to executing a SQL Agent job.

http://www.mssqltips.com/sqlservertip/1730/different-ways-to-execute-a-sql-agent-job/

Hope this helps. Kosh

Upvotes: 2

Related Questions