Call the SSIS package when a record inserted into the table

We have one requirement in that the business way to run the scheduled job when we insert a record into the custom table.

There is a job which is scheduled to run every Sunday but some time business want to run this package in weekdays when we insert a record into a table (Manual_Job_run_history)

Structure of Manual_Job_run_history table

create table Manual_Job_run_history   (RunID int identity(1,1),   Executed_by varchar(50),   Rundate datetime    )

With the help of the trigger, we can achieve this task

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERTING, UPDATE, or DELETE statements on a table or view.

Read More about Trigger Here

https://bageshkumarbagi-msbi.blogspot.com/2017/08/trigger-in-sql-server.html

The job is scheduled on the Sql server Agent

  

In this job calling a simple package.

in this package, I am inserting a record into a table.

  

Inserting a table into this package

Now I am running this package

Package executed successfully.

See the record into the table

Now I am creating a trigger on the Manual_Job_run_history table on insert event

Create trigger trg_Manual_Job_run_history_insert on Manual_Job_run_history for insert AS BEGIN EXEC msdb.dbo.sp_start_job N'TestJob' END

The trigger is created.

   

    

Now I am inserting a record into this table

Before inserting a record into the Manual_Job_run_history table see the records in the BadData table

  

Now inserting a record into the table

  

See job is executed successfully

     

Record is also inserted into the BadData table

  

We can see the job execution details in the Job history

  

Hope it will help.

Thanks!