SSIS Package Map

Overview

The master and child structure of an ETL estate is the thing nobody has written down and everyone needs when they change something. Where it has been written down it is out of date.

This is built from last month’s executions, which is the only version that is true.

Where to find it

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

The mechanism

A package started inside another by an Execute Package Task running in process shares its parent’s execution.

So the distinct package names inside one execution are its call graph, and catalog.executions.package_name is the root of it.

Nothing else in the catalog records the relationship at all.

Two findings the graph produces on its own

Drift from what the project was designed to be

  • A package marked as an entry point that is only ever called by another.
  • A package marked as a child that somebody starts directly.

Both are invisible from SSIS Catalog Inventory, which can only see whether a package ran.

The orphan

A package neither called nor started.

Inventory reports that as never run; this page says why, because it can tell “nothing starts it” from “nothing calls it”.

The honesty

Only edges that ran are edges.

  • A child called from a branch nothing took this month is not in the graph.
  • An Execute Package Task set to run out of process starts its own execution and never appears in its parent’s executables at all.

Reading the grid

Column What it is
Package The package.
What it is Root, intermediate, leaf, or orphan, from its position in the observed graph.
Called by Which packages called it, and how often.
Calls Which packages it called.
Runs How many executions it appeared in.
Tasks How many executables it contributed.
Nesting How deep it sits below a root.
Declared Its entry_point setting, which is what it was designed to be.
State Matches its declaration, or has drifted from it, or is an orphan.

Declared against What it is, side by side, is the drift finding.

The toolbar

Control Options Default
Window 7 days, 30 days, 90 days 30 days
The map as text Copies the graph as an indented tree, for pasting into a document.
Inventory Opens SSIS Catalog Inventory.

Where the data comes from

Source What it gives
catalog.executables Every executable inside an execution, with its package_name, which is what the graph is built from.
catalog.executable_statistics The order and nesting inside one execution.
catalog.executions The root package of each execution.
catalog.packages entry_point, which is the declared intent.
Report Why you would go there
SSIS Catalog Inventory What is deployed, and what has never run.
SSIS Inside the Package The task level detail inside one of these executions.
SSIS Packages Run The executions the graph was derived from.
SSIS Connection Reach The blast radius, once you know the call structure.
SSIS Critical Path Which package in the chain decided when the night ended.

Frequently asked questions

A package I know is called does not appear. Either the branch that calls it did not run in the window, or its Execute Package Task runs out of process, which starts a separate execution and leaves no trace of the relationship.

Why not read the packages instead? Because the packages are in the .ispac, which SSISDB stores as a blob it does not parse. What ran is the only structure the catalog can see, and it is also the only one that is current.

An entry point package is only ever called. Somebody built it to be started and it is now a child. That is not broken, but the declaration and the reality disagree, and whoever maintains it should know.

What is an orphan? A package nothing starts and nothing calls. It is deployed, it costs version chain and cleanup, and nothing runs it.