Triggering loads using SQL Server Agent has some limitations. For one, we cannot run the job more than once at a time. Another limitation is that it is hard to trigger jobs programmatically. There is a stored procedure called sp_Start_Job that can do it. It starts the job but doesn't wait for it to complete, so we can't have the execution status (success, failed) returned by the job. We can use TSQL scripts that pool the job status while it's executing but still, it's not an out-of-the-box solution.
When using SSIS project-model deployment, package execution can be called via TSQL. This recipe will show you how it can be done.
