SSIS Schedule
Overview
Two views, answering two questions a duration never can.
| View | The question |
|---|---|
| Week grid | Seven days by twenty four hours. A batch window has a shape, and so does a Sunday that quietly stopped running in April. |
| At once | How many executions were live at each moment. Ten packages that each take a minute are fine one after another and are a memory problem all at once. |
The SSIS host is where that second one goes wrong, rather than SQL Server.
Where to find it
Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Schedule.
The Agent job match is an inference, and is labelled as one
The catalog does not record the Agent job that started a run. There is no column for it.
What there is, is the job step. An Agent step of the SSIS subsystem carries the package path in sysjobsteps.command:
/ISSERVER "\"\SSISDB\Folder\Project\Package.dtsx\""so the match is made on that. Everything matched that way is labelled as matched, and everything else is labelled as a manual or T-SQL caller. The page never presents the inference as a fact.
The memory the catalog already captured
catalog.executions captures total_physical_memory_kb and available_physical_memory_kb at the start of each run, on the host named in machine_name.
Ten packages starting at two in the morning on a box with 900 MB free is a finding the duration numbers will never explain, and it is sitting in a column the built in reports never surface.
It is host memory, not SQL Server memory, and on a remote SSIS host it describes a machine this product is not connected to, so the column is labelled with the host name.
Reading the grid
| Column | What it is |
|---|---|
| Package | The package. |
| Agent job | The job matched by the /ISSERVER command, or not matched to a job where none matched. |
| Project | The folder and project the package belongs to. |
| Runs | Executions in the window. |
| Typical | Its average duration (total run time divided by runs), so the grid cell has a weight behind it. |
| When | The hours and days it runs, in words. |
| Least free on the host | The lowest available_physical_memory_kb recorded at the start of any of its runs. |
| Host | machine_name, the SSIS host. |
| Started by | The caller recorded by the catalog. |
Runs and Typical sort by value, so the slowest package sorts to the top of Typical whichever unit its time is shown in.
A run on the chart is one execution and a grid row is one package, so a run leads to its package’s row.
- Click a run to select its package’s row in the grid.
- Double click a run to select its package’s row and open SSIS Packages Run.
- Right click a run for the same menu as its package’s grid row: Recent runs, How long it takes, Its failures (when any failed), How the Agent jobs were matched, Copy job name (when a job was matched) and Copy package path.
- Right click an empty part of the chart to copy the chart to the clipboard.
The toolbar
| Control | Options | Default |
|---|---|---|
| Window | 7 days, 30 days | 30 days |
| View | Week grid, At once | Week grid |
| Agent jobs matched | Shows which job matched which package, and which did not match at all. |
Where the data comes from
| Source | What it gives |
|---|---|
catalog.executions |
Start, end, machine_name, total_physical_memory_kb, available_physical_memory_kb, caller. |
msdb.dbo.sysjobsteps |
The /ISSERVER command strings, matched against the package path. |
msdb.dbo.sysjobs, dbo.sysschedules |
The job and schedule names behind a match. |
Related reports
| Report | Why you would go there |
|---|---|
| SSIS Self Contention | Whether a package is actually slower when others run beside it. |
| SSIS Critical Path | Which of the packages in the window decided when the night ended. |
| SSIS Packages Run | The same executions as a timeline rather than a grid. |
| SSIS Job Disagreement | Whether the matched Agent job reported the package’s result honestly. |
| Job Schedules | The Agent side of the schedule. |
Frequently asked questions
A package shows “manual or T-SQL” and I know a job runs it. Then the step does not use the /ISSERVER form, or it calls catalog.start_execution from T-SQL. Both are legitimate, and neither leaves a match for this page to make.
The memory figures look wrong. They are the SSIS host’s memory, read at the start of the run, not SQL Server’s. On a remote SSIS host they describe a machine this product never connects to, which is why the column carries the host name.
The week grid has a hole. Look at the day rather than the package. A Sunday that stopped running in April is the shape this view exists to show.
Why does concurrency matter more than duration? Because the SSIS host runs each execution in its own process. Ten of them at once is ten processes competing for memory on a machine nobody is watching.