SSIS Data Flow

Overview

The strongest single finding available anywhere in SSISDB is on this page: a path whose rows went to zero while the package still reported success.

A source query that was truncated, a filter that no longer matches, a file that arrived empty. All of them look like a clean green run on every other page in this product and on every page of the reports shipped with SQL Server. Here they are a line hitting the axis.

Where to find it

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

The logging levels this page needs

The catalog does not write this data by default, and the page says so rather than drawing an empty chart.

What Where Written at
Row counts catalog.execution_data_statistics Verbose only.
Component phase times catalog.execution_component_phases Performance and above.

SSIS Overrides is where a logging level raised for an investigation and left there shows up, and SSIS Who Fills It is what that costs.

Two traps this page is built around

One row per buffer, not one per run

execution_data_statistics writes one row per buffer. A path that moved a million rows in hundred thousand row buffers has ten rows, each saying 100000, and reading any one of them gives a tenth of the answer.

Everything here sums per path per execution first.

The same rows are counted again at every path

A source feeding a transform feeding a destination writes one buffer under three path names.

So this page reports per path rather than per package, and never adds the paths of one data flow together. That is also why SSIS Package Speed takes the widest single path rather than the sum.

The two views

View What each row is
Rows moved One row per data flow path: where the rows came from, where they went, and how many there were.
Component time One row per component: how long it held the buffer, and in which phase.

Reading the grid

Rows moved

Column What it is
Path The data flow path name.
Package / Task Which data flow task it is inside.
Source / Destination The components either end of the path.
Runs How many executions contributed.
Latest rows The most recent run’s total for this path.
Typical rows The median across the window. Latest against typical is the finding.
Rate Rows a second.
State Whether this path is steady, has grown, has shrunk, or has gone to zero.

Component time

Column What it is
Component The component name.
Package / Task Where it lives.
Active time How long it held the buffer.
Share That time as a share of the data flow.
Phases How many phases it reported.
Busiest phase Which phase cost the most: PrimeOutput, ProcessInput, PreExecute and the rest.

The toolbar

Control Options Default
Window 24 hours, 7 days, 30 days 7 days
View Rows moved, Component time Rows moved
Catalog health Opens SSIS Catalog Health, because Verbose logging is what fills SSISDB.

Where the data comes from

Source What it gives
catalog.execution_data_statistics rows_sent per buffer, per path, per execution, with the source and destination component names.
catalog.execution_component_phases Phase start and end per component per execution.
catalog.executions The execution and its logging level.
Report Why you would go there
SSIS Inside the Package The task level breakdown around the data flow.
SSIS Package Speed The duration this row count explains.
SSIS Catalog Health What Verbose logging is doing to SSISDB.
SSIS Who Fills It Which package is writing all of it.
SSIS Overrides Where a logging level was raised and left.

Frequently asked questions

The page says there is nothing to show and my packages ran. The catalog was not logging at Verbose. Row counts exist at Verbose only, and nothing else in SSIS records them.

A path moved zero rows and the package succeeded. That is the finding this page exists for. The package did what it was told; what it was told to move was empty.

Why not add the paths together for a package total? Because a source, a transform and a destination are three paths carrying the same rows. Adding them triples the answer on every package with a transform in it.

Should I turn Verbose on everywhere? No. It is the usual reason SSISDB becomes the largest database on the instance. Turn it on for the package you are investigating, and use SSIS Overrides to check that it went back.