Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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
Related Tasks
Troubleshooting Tools for Package Execution
Related Content
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services