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. |
| State | Matches its declaration, or has drifted from it, or is an orphan. |
| Called by | Which packages called it. |
| Calls | Which packages it called. |
| Declared | Its entry_point setting, which is what it was designed to be. |
| Runs | How many executions it appeared in. |
| Tasks | How many executables it contributed. |
| Nesting | How deep it sits below a root. |
Declared against What it is is the drift finding, and State says which way it drifted.
Layout. State comes straight after What it is, so the finding is on screen at 1280 x 900 without scrolling sideways. Called by and Calls show up to four names each and are capped, and State takes all the width that is left (never less than 250 px). The tooltip on a row lists every caller and every callee in full, with how often, along with the package path, what it is, its declaration and its state.
- Click a bar to select that package’s row in the grid.
- Double click a bar to select that row and open SSIS Packages Run, or SSIS Catalog Inventory for a package that never ran, the first page the row’s right click menu goes to.
- Right click a bar for the same menu as that package’s grid row: The whole map as text, Its runs (when it ran), What is deployed 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, 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. |
Related reports
| 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.