SSIS Script Task Excel Instance Not Closing

Question

So I have a Script Task in SSIS which opens, refreshes, closes, and saves an Excel file. It does this using Visual Basic. It works perfectly and does exactly what I want it to do. However, I can not get the Excel instance itself to close and I've been trying to figure out why for hours. Here's the current code:

Public Sub Main()

    'On Error GoTo Quit

    Dim excel As New Microsoft.Office.Interop.Excel.Application
    Dim wb As Microsoft.Office.Interop.Excel.Workbook

    excel.DisplayAlerts = False
    wb = excel.Workbooks.Open("FilePath")

    wb.Sheets("Sheet Name").Cells(2, 18).Value = "thru " & IIf((Month(DateAdd("D", -1, Today)) < 10), "0" & Month(DateAdd("D", -1, Today)), Month(DateAdd("D", -1, Today))) & "/" & Format(Convert.ToDateTime(DateAdd("D", -1, Today)), "dd") & "/" & Format(Convert.ToDateTime(DateAdd("D", -1, Today)), "yyyy")

    wb.Sheets("Sheet Name").PivotTables("PivotTable").PivotCache.Refresh()
    wb.Sheets("Sheet Name").PivotTables("PivotTable").PivotCache.Refresh()
    wb.Sheets("Sheet Name").PivotTables("PivotTable").PivotCache.Refresh()
    wb.Sheets("Sheet Name").PivotTables("PivotTable").PivotCache.Refresh()
    wb.Sheets("Sheet Name").PivotTables("PivotTable").PivotCache.Refresh()
    wb.Sheets("Sheet Name").PivotTables("PivotTable").PivotCache.Refresh()

    'Choose arrow based on criteria
    If wb.Sheets("Sheet Name").Cells(13, 16).Value > wb.Sheets("Summary").Cells(13, 14).Value Then
        wb.Sheets("Sheet Name").Shapes("Shape").Copy()
        wb.Sheets("Sheet Name").Cells(12, 13).Select()
        wb.Sheets("Sheet Name").Paste()
    Else
        wb.Sheets("Sheet Name").Shapes("Shape").Copy()
        wb.Sheets("Sheet Name").Cells(12, 13).Select()
        wb.Sheets("Sheet Name").Paste()
    End If

    wb.Sheets("Sheet Name").Shapes("Shape").Delete()
    wb.Sheets("Sheet Name").Shapes("Shape").Delete()

    wb.Sheets("Sheet Name").Cells(2, 2).Select()

    wb.SaveAs("FilePath")

    wb.Close()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)

    excel.DisplayAlerts = True
    excel.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)

    'wb = Nothing
    'excel = Nothing

    'GC.Collect()

    Dts.TaskResult = ScriptResults.Success

    'Quit:   excel.Quit()

End Sub

Can anyone figure out why this would not close the instance?

Thanks

Answer 1

You're leaking a ton of COM objects, that's why.

wb = excel.Workbooks.Open("path")

This leaks the Workbooks collection object.

wb.Sheets("Summary").Cells(2, 18).Value = something

This leaks the Sheets collection object, the Worksheet object retrieved from it, and the Range object obtained from that Cells call.

wb.Sheets("Summary").Shapes("Down Arrow 3").Copy()

Again, leaking the Sheets collection, the retrieved Worksheet instance, the Shapes collection, and the retrieved Shape object.

This is interop between COM and .NET: you're not in VBA-land here, you're not in-process either.

You can't chain member calls like this. You need to hold a reference to every COM object you access, and properly release it.

System.Runtime.InteropServices.Marshal.ReleaseComObject(wb)

That's the workbook, ...but then there are now runtime callable wrappers (RCWs) disconnected from their underlying COM object, lingering in managed memory - that is what's keeping the EXCEL.EXE process alive: these COM objects aren't accessible anymore, and there's nothing to release them.

Split the chained member calls.

Dim wbSheets As Microsoft.Office.Interop.Excel.Worksheets = wb.Sheets
Dim summarySheet As Microsoft.Office.Interop.Excel.Worksheet =wbSheets("Summary")
'...

Then release these as well:

System.Runtime.InteropServices.Marshal.ReleaseComObject(wbSheets)
System.Runtime.InteropServices.Marshal.ReleaseComObject(summarySheet)
'...

For all of them. Then the process should exit cleanly.