SSIS Completion Trend

Overview

What time does the overnight batch actually finish, and is it drifting later?

Every monitoring system in an estate watches for failure. Almost none watch for a batch that still succeeds and finishes twenty minutes later every month, because there is no event to catch and nothing to alert on.

The catalog has had the data since the day it was created.

Where to find it

Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Completion Trend.

The axis is a clock, not a duration

That is the whole design.

A batch that starts late and takes exactly as long as it always does still finishes late, and a duration chart draws that as a flat line.

Later is lower, so drift walks downhill, which is the direction everybody already reads as getting worse.

Two decisions worth recording

A night is a thirty hour window ending at midday

Not a calendar day.

A batch that starts at 22:00 and ends at 04:00 belongs to one night and falls either side of midnight, and grouping by date would cut it in half and report two.

The set being watched is a folder or a project, chosen

Not the whole catalog.

Taking the latest finish across everything makes one ad hoc afternoon run look like the batch overrunning by ten hours, which is how a page like this stops being read.

The toolbar’s Watching control is what picks the set, and the choice is remembered.

Reading the grid

Column What it is
Night The thirty hour window, named by the date it mostly covers.
Finished The clock time the last package in the watched set finished.
Against typical How far that is from the median finish across the window.
Last package Which package finished last that night.
Started When the first package in the set started, so a late finish can be told from a late start.
Elapsed First start to last finish.
Runs How many executions were in the set that night.
Failed How many of them failed.
State On time, drifting later, late, or a night that did not run.

Finished against Started is the diagnosis. A later finish with the same elapsed is a start problem; a later finish with a longer elapsed is a work problem.

The toolbar

Control Options Default
Window 30 days, 90 days, 6 months 90 days
Watching The folder or project being watched. The busiest folder
Critical path Opens SSIS Critical Path for the selected night.

Where the data comes from

Source What it gives
catalog.executions Start, end and status per execution, grouped into nights.
catalog.folders, catalog.projects The sets available to watch.
catalog.catalog_properties The retention window, which is the real limit on how far back the trend can go.
Report Why you would go there
SSIS Critical Path Which package made a particular night end when it did.
SSIS Package Speed Whether the package on the end of the chain is slowing down.
SSIS Schedule Whether the start time moved rather than the work growing.
SSIS Self Contention Whether the batch is slower because more of it runs at once.
SSIS Packages Run One night in full.

Frequently asked questions

Why is later drawn lower? Because downhill already means getting worse to every reader. An axis where 05:00 is below 04:00 makes drift look like drift without a legend.

The line jumps on one night. Look at Started. A late start with a normal elapsed is a different problem from a normal start with a long elapsed, and both are visible in the same row.

Can I watch the whole catalog? You can, and it is usually a mistake. One ad hoc afternoon run becomes the night’s last finish and the trend stops meaning anything.

Six months is greyed out. The catalog’s retention window is shorter than that. SSIS Catalog Health is where it is set.