SSIS Inside the Package
Overview
A package that takes eleven minutes almost never takes eleven minutes everywhere. It is one task, or it is one task called four thousand times.
Those two look identical from the Speed page and have nothing in common as fixes, and catalog.executable_statistics has known which it was the whole time.
Where to find it
Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Inside the Package.
Or drill through from SSIS Package Speed or SSIS Packages Run, which opens this page already pointed at the package you were looking at.
The free hierarchy
execution_path is a string like:
\Package\Foreach Loop[3]\Data Flow TaskSplitting on the backslash gives the container tree without reading the package, and stripping the indexers groups every iteration of a loop body under one row.
That is how the loop ran four thousand times, forty milliseconds each becomes a sentence rather than four thousand rows.
Two things this page is careful about
A parent and its children share one execution id
A package started inside another by an Execute Package Task writes its executables into the same execution, so the rows arrive as one interleaved tree that never existed.
They are grouped by package name first, so what you read is one package at a time.
execution_result has four values, not two
Completion is a task whose precedence constraint was On Completion rather than On Success, which is to say one the author decided could end either way.
Coloring it red would invent a problem somebody has already decided is not one.
The two views
| View | What it shows |
|---|---|
| Where the time went | Tasks ranked by total time. The tuning view. |
| In order | The tasks on a time axis, in the order they ran, as lanes. The view for understanding the shape of a run. |
Reading the grid
| Column | What it is |
|---|---|
| Task | The executable name, with loop indexers stripped. |
| Path | The full execution_path, which is where in the container tree it sits. |
| Package | Which package it belongs to, for an execution that contains several. |
| Times | How many times it ran. A large number here is a loop. |
| Total | The sum of its durations. |
| Share | That total as a share of the execution. |
| Longest | The longest single call. Compare against Total divided by Times. |
| Result | Success, failure, completion or canceled, all four kept apart. |
| First started | When it first ran inside the execution. |
The toolbar
| Control | Options | Default |
|---|---|---|
| View | Where the time went, In order | Where the time went |
| Recent runs | Opens SSIS Packages Run. | |
| Data flow | Opens SSIS Data Flow. |
Where the data comes from
| Source | What it gives |
|---|---|
catalog.executable_statistics |
One row per executable per iteration: start, end, execution_result, execution_duration. |
catalog.executables |
The executable names and their package_name. |
catalog.executions |
The execution this all belongs to. |
executable_statistics is written at Basic and above, so this page works on a default catalog. SSIS Data Flow is the one that needs more.
Related reports
| Report | Why you would go there |
|---|---|
| SSIS Package Speed | Whether this run was unusual for this package. |
| SSIS Data Flow | Inside a Data Flow Task: which path moved the rows and which component held the buffer. |
| SSIS Package Map | The parent and child structure this execution contains. |
| SSIS Package Failures | The task that raised the error, on the same execution_path. |
| SSIS Critical Path | Whether this package is worth tuning at all. |
Frequently asked questions
One row has a huge Times count. That is a loop body. Total divided by Times is what one iteration costs, and Longest says whether one iteration was an outlier or they are all the same.
I see tasks from a package I did not open. Its parent called it in process, so they share one execution. The rows are grouped by package so you can read them apart.
A task is marked Completion and I expected Success. Its precedence constraint was On Completion. The author allowed it to end either way, so the page does not call it a problem.
The Data Flow Task shows as one row with a big total. This page sees a data flow as one executable. SSIS Data Flow is what opens it up into paths and components.