Analysis of Data Flow
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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.
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