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

MORE INFORMATION

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("Min=" & CStr(min) & vbCrLf & "Max=" & CStr(max) & vbCrLf & "Mean=" & CStr(min + max \ 2))

        End If

    End Sub

End Class