如何以类SET STATISTICS的方式监控SSIS数据流，并对比SQL Server T-SQL与SSIS的执行统计信息

Great question—capturing the same kind of execution metrics (scan counts, read/write volumes, CPU time, duration) that you get from SET STATISTICS IO/TIME in T-SQL is totally feasible with SSIS, though the approach differs a bit since SSIS operates as an ETL tool with its own monitoring layers. Here are the most effective methods to get those stats:

1. Enable SSIS Logging for Pipeline-Level Statistics #

SSIS has built-in logging that can capture detailed execution metrics for your data flow task, including row counts, read/write operations, and timing data. Here's how to set it up:

• Right-click your SSIS package in Visual Studio > Select Logging.
• Choose a log provider (e.g., SQL Server, Text File) and link it to your data flow task.
• Under the Details tab, check these key events for your data flow:
  • PipelineExecutionPlan: Shows the execution plan for the data flow, including source/destination scan details.
  • PipelineExecutionStatistics: Returns real-time stats like rows read, rows written, buffer usage, and elapsed time for each component.
  • OnInformation: Captures general execution details, including timing for task start/end.
• Run the package, then review the log to see metrics that align with what you get from T-SQL's STATISTICS commands.

2. Use SSMS's SSIS Execution Dashboard (For Deployed Packages) #

If you deploy your SSIS package to the SSIS Catalog (SSISDB), you can use SSMS to access a rich execution dashboard:

• In SSMS, expand the Integration Services Catalogs > SSISDB > Your folder > Projects > Your project > Packages.
• Right-click your package > Reports > Standard Reports > All Executions.
• Select the most recent execution, then drill into the Data Flow Task details. You'll see:
  • CPU time and elapsed duration for each component.
  • Rows read from sources and written to destinations.
  • Buffer memory usage (which correlates to IO overhead).
    For locally run packages, the Progress tab in Visual Studio will show real-time row counts and task duration as the package runs.

3. Query SSIS Catalog Views with T-SQL #

For programmatic access to stats (great for side-by-side comparison with your original T-SQL results), use the SSIS Catalog system views. These views store detailed execution data for deployed packages:
Here's a sample query to get component-level stats:

SELECT
    e.execution_id,
    e.package_name,
    ec.component_name,
    ec.reads AS logical_reads,
    ec.writes AS logical_writes,
    ec.cpu_time,
    ec.elapsed_time,
    ec.rows_read,
    ec.rows_written
FROM
    catalog.execution_component_statistics ec
JOIN
    catalog.executions e ON ec.execution_id = e.execution_id
WHERE
    e.package_name = 'YourPackageName.dtsx'
ORDER BY
    e.start_time DESC;

This will return metrics that map directly to what SET STATISTICS IO/TIME provides—you can even join this data with your T-SQL stats for a direct comparison.

4. Run Your Original T-SQL in an Execute SQL Task (With STATISTICS Enabled) #

Since your SSIS package is essentially executing that join-and-INSERT logic, you can replicate the exact T-SQL stats by wrapping your query in SET STATISTICS commands inside an Execute SQL Task:

• Create an Execute SQL Task in your SSIS package.
• Set the SQLStatement property to:

  SET STATISTICS IO ON;
  SET STATISTICS TIME ON;
  INSERT INTO [myDB].dbo.finalTable WITH (TABLOCK) (id, description, value) 
  SELECT a.id, a.description, b.value 
  FROM [anotherDB].dbo.sourceA a 
  INNER JOIN [anotherDB].dbo.sourceB ON a.id = b.id;
  

• Enable logging for the Execute SQL Task (or check the Progress tab). The STATISTICS output will appear as information messages in the SSIS logs, showing the same scan counts, read/write totals, CPU time, and duration you see when running the T-SQL directly in SSMS.

Each method has its use case: use SSIS logging/dashboard for ETL-specific component stats, or the Execute SQL Task approach if you want a 1:1 comparison with your original T-SQL execution metrics.

内容的提问来源于stack exchange，提问作者Damian Collier