I have an Excel vba app that I have used for years, which builds reports from data it retrieves from a Sql Server warehouse. It has run tens of thousands of reports.
Now I'm pulling the core vb code over to SSIS and building a script to do the same operation. It runs well overall but one routine is very slow. It works, but is super slow on big files, and I have some with hundreds of thousands of rows..
I am using VB script 2012, and it is essentially the exact same subroutine as the vba app, and can't figure out why it is so much slower. I am turning off screen refresh and calculations, so that isn't the issue.
Here is the core loop that is slower. I'm alternating background color on a column value, so when the value changes, it toggles back to the background color, or turns it off, until it hits a blank value.
Can anybody guess why this is slower in SSIS? I am referencing Microsoft.Office.Interop.E
If UCase(.Cells(iRow, iTriggerCol).Value) <> UCase(.Cells(iStartRow, iTriggerCol).Value) Then
'change, so switch
bHasChanged = True
oRng = .Range(oWS.Cells(iStartRow, 1), oWS.Cells(iRow - 1, iLastCol))
If bColorMe Then
oRng.Interior.Color = 16772300 'light blue
bColorMe = False
bColorMe = True
With oWS.Range(oWS.Cells(iRow, 1), oWS.Cells(iRow, iLastCol)).Borders(-4160)
.LineStyle = 1 'xlContinuous
.Color = RGB(0, 0, 0)
.Weight = 2 'xlThin
iStartRow = iRow
'drop out if there are no changes after a thousand rows...
If bHasChanged = False And iRow > 1000 Then Exit Do
'If .Cells(iRow, iTriggerCol).Value = "" Then Exit Do
If iRow = iLastRow Then Exit Do
iRow = iRow + 1
If .Cells(iRow, iTriggerCol).Value = "" Then Exit Do