Microsoft KB Archive/911838

= How to measure the rate at which rows pass through a particular data flow task in a SQL Server 2005 Integration Services (SSIS) package =

Article ID: 911838

Article Last Modified on 12/16/2005

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition

-





INTRODUCTION
You may want to measure the rate at which rows pass through a particular data flow task in a Microsoft SQL Server 2005 Integration Services (SSIS) package. However, you cannot use the Performance Monitor tool (Perfmon.exe) to monitor this performance by adding counters in the SQLServer:SSIS Pipeline object. This article describes a simple method that you can use to measure the rate. You can use a Script component data flow transformation in the data flow. The Script component data flow transformation contains a script that returns the minimum rate, the maximum rate, and the median rate at which rows pass through the data flow task.



MORE INFORMATION
To measure the rate at which rows pass through the data flow task, use the following script in a Script component data flow transformation in the Integration Services package. Imports System

Imports System.Data

Imports System.Data.OleDb

Imports System.Collections

Public Class ScriptMain

Inherits UserComponent

Private startTicks, totalTicks As Long

Private rowCount, totalRows As Integer

Private rps As New ArrayList 'rps = rows per second

Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)

'Save the rate statistic for this buffer

If startTicks <> 0 Then

totalRows += rowCount

Dim ticks As Long = CLng(DateTime.Now.Ticks - startTicks)

If ticks > 0 Then

totalTicks += ticks

Dim rate As Integer = CInt(rowCount * (TimeSpan.TicksPerSecond / ticks))

rps.Add(rate)

End If

End If

'Reinitialize the counters

rowCount = 0

startTicks = DateTime.Now.Ticks

'Call the base method

MyBase.Input0_ProcessInput(Buffer)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

rowCount += 1 'No exposed Buffer.RowCount property. Therefore, you have to count manually

End Sub

Public Overrides Sub PostExecute

MyBase.PostExecute

'Only write the extended stats if RowCount > 0

If rps.Count > 0 Then

'Calculations depend on sorted array

rps.Sort

'Remove boundary-case statistics

If rps.Count >= 3 Then rps.RemoveAt(0)

'Calculate min and max

Dim min As Integer = CInt(rps.Item(0))

Dim max As Integer = CInt(rps.Item(rps.Count - 1))

'Display results

MsgBox(&quot;Min=&quot; & CStr(min) & vbCrLf & &quot;Max=&quot; & CStr(max) & vbCrLf & &quot;Mean=&quot; & CStr(min + max \ 2))

End If

End Sub

End Class Note In this code, the MsgBox function is used to display the statistics results. However, when you use this code in a real implementation, it may be useful to output the statistics to a table that can be used for trend analysis.

