Using And Disposing Excel Interop Assembly With VB.Net In SSIS

What is the proper method of using the Excel.Interop assemblies with SSIS? I am using SQL Server 2005 Integration Services (VS 2005) against the .NET Framework v2.0.50727 with the Microsoft.Office.Interop.Excel.dll assembly.

I find that the referenced instance of Excel in my SSIS package is not always released from memory as expected. I'm not sure that I have a problem per se, but I am interested in knowing the Microsoft reccommended method of using the Office Interop assemblies with SSIS.

For this thread, "working" code is defined as observing the appearance and disappearance of the Excel.exe image in the Task Manager as it is created and destroyed in the SSIS package.

The following Sript Task code (between stars) works as expected in that the image of Excel can be seen created and removed from the Task Manager list of processes:

************************************************

Imports Microsoft.Office.Interop.Excel

Public Sub Main()

Dim mobjExcelApp As Microsoft.Office.Interop.Excel.Application

Dim mobjWorkbook As Microsoft.Office.Interop.Excel.Workbook

Dim objSheet as Worksheet

Dim strExcelFilename as string = "C:TempMyExcelfile.xls"

Dim strSheetname_ValidSamples as String = "Sheet1"



mobjExcelApp = New Microsoft.Office.Interop.Excel.Application

mobjExcelApp.Visible = False

mobjWorkbook = OpenWorkbook(strExcelFilename)

objSheet = CType(mobjWorkbook.Worksheets(strSheetname_ValidSamples), Worksheet)

strWell = CType(objSheet.Range("a1").Offset(intRow, 0).Value, String)

objSheet = Nothing

mobjWorkbook.close

mobjWorkbook = Nothing

mobjExcelApp.Quit

mobjExcelApp = nothing

System.GC.Collect()

System.GC.WaitForPendingFinalizers()

System.GC.Collect()

System.GC.WaitForPendingFinalizers()

End Sub

***********************

On occasion, when the line of code that sets the variable strWell (in BLUE above) is followed with the following line of code:

strSampleName = CType(objSheet.Range("a1").Offset(intRow, 1).Value, String)

the code will work fine with no errors, however, the Excel image is not removed from the task list. I have found that the instance will disappear upon waiting (several minutes or more).

Thanks,

Rob