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 / Project The package.
Runs Executions in the window.
When The hours and days it runs, in words.
Typical Its median duration, so the grid cell has a weight behind it.
Started by The caller recorded by the catalog.
Agent job The job matched by the /ISSERVER command, or manual or T-SQL where none matched.
Host machine_name, the SSIS host.
Least free on the host The lowest available_physical_memory_kb recorded at the start of any of its runs.

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.
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.