Analysis of Data Flow

  • Article

You can use the catalog.execution_data_statistics SSISDB database view to analyze the data flow of packages. This view displays a row each time a data flow component sends data to a downstream component. The information can be used to gain a deeper understanding of the rows that are sent to each component.

Note

The logging level must be set to Verbose in order to capture information with the catalog.execution_data_statistics view.

The following example displays the number of rows sent between components of a package.

use SSISDB
select package_name, task_name, source_component_name, destination_component_name, rows_sent
from catalog.execution_data_statistics
where execution_id = 132
order by source_component_name, destination_component_name

The following example calculates the number of rows per millisecond sent by each component for a specific execution. The calculated values are:

  • total_rows - the sum of all the rows sent by the component

  • wall_clock_time_ms – the total elapsed execution time, in milliseconds, for each component

  • num_rows_per_millisecond – the number of rows per millisecond sent by each component

The HAVING clause is used to prevent a divide-by-zero error in the calculations.

use SSISDB
select source_component_name, destination_component_name,
    sum(rows_sent) as total_rows,
    DATEDIFF(ms,min(created_time),max(created_time)) as wall_clock_time_ms,
    ((0.0+sum(rows_sent)) / (datediff(ms,min(created_time),max(created_time)))) as [num_rows_per_millisecond]
from [catalog].[execution_data_statistics]
where execution_id = 132
group by source_component_name, destination_component_name
having (datediff(ms,min(created_time),max(created_time))) > 0
order by source_component_name desc

Debugging Data Flow

Troubleshooting Tools for Package Execution

Related Content

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Concepts

Data in Data Flows