SSIS Slow Down Performance Script Component

I’ve been meaning to post this for a while, but real life keeps intruding. I did a presentation on SSIS performance for the Charlotte SQL Server User Group at the end of November. It went well, though I was trying to squeeze a 90 minute presentation into 60 minutes, so I had to skim over some of the good stuff. You can find the presentation here (it’s the first link, at least till we have another meeting).

One item that I did need for the demos in the presentation was the ability to slow down the data flow. Normally, SSIS processes so fast that you can’t really see what’s happening. I found a few examples of script components that introduced a delay every time a buffer was processed. Unfortunately, that wasn’t flexible enough for me, as part of my demo was adjusting the numbers of rows per buffer. I really wanted something that would give a delay every X rows, while being easy to configure without having to edit the script each time. I ended up writing the following code:

Public Class ScriptMain
    Inherits UserComponent

    Private RowCount As Integer
    Private rowDelay As Integer
    Private everyNRows As Integer

    Public Overrides Sub PreExecute()
        MyBase.PreExecute()
        Dim vars As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90
        Me.VariableDispenser.LockForRead("EveryNRows")
        Me.VariableDispenser.LockForRead("MSTimeDelay")
        Me.VariableDispenser.GetVariables(vars)
        rowDelay = CType(vars("MSTimeDelay").Value, Integer)
        everyNRows = CType(vars("EveryNRows").Value, Integer)
        vars.Unlock()
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        RowCount += 1

        If RowCount Mod everyNRows = 0 Then
            System.Threading.Thread.Sleep(rowDelay)
        End If
    End Sub
End Class

This relies on two integer variables to be declared in the package. EveryNRows specifies how many rows should be processed before introducing a delay. MSTimeDelay sets how long the the delay should be, in milliseconds.

One thing to be aware of – in the above script, I am reading the package variables in the PreExecute method and storing them in instance variables. Locking and unlocking the variables is time consuming, and will drastically slow things down if you do it in the ProcessInputRow method.