Analysis of Data Flow
Applies To: SQL Server 2016 Preview
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