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.