Near real-time and on-demand loads
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.
Getting ready
This recipe assumes that you have deployed the two sample SSIS projects in the SSIS catalog.
How to do it...
- Open SSMS if not already open and go to the SSIS catalog node. Expand it and navigate to the
EP_Staging
entry-point package. Right-click on it and selectExecute
from the menu that appears. - The
Execute Package
window...