Unable to close excel even after releasing resources

Question

I have tried many solution but didn't work Please help me to fix this issue. Below is the code used in SSIS Script Task

    using Excel = Microsoft.Office.Interop.Excel;        
            Excel.Application xlApp = null; 
            Excel.Workbooks workbooks = null;
            Excel.Workbook xlWorkbook = null;
            Excel.Worksheet worksheet = null;
            Excel.Range xlRange = null;                           
        try
          {

            xlApp = new Excel.Application();
            xlApp.DisplayAlerts = false;
            xlApp.AskToUpdateLinks = false;
            workbooks = xlApp.Workbooks;
            xlWorkbook = workbooks.Open("sample.csv", 2, true);
            xlWorksheet = xlWorkbook.Sheets[1];
            xlRange = xlWorksheet.UsedRange;
            int rowCount = xlRange.Rows.Count;
            for (int row = 2; row <= rowCount; row++)
            {
              //some logic

            }
           }
    catch (Exception ex)
    {
       MessageBox.Show(ex.Message);
    }
    finally  //releasing all resources
    {
       GC.Collect();
       GC.WaitForPendingFinalizers();
       Marshal.ReleaseComObject(xlRange);
       Marshal.ReleaseComObject(xlWorksheet);
       xlRange = null;
       xlWorksheet = null;
       xlWorkbook.Close();
       Marshal.ReleaseComObject(xlWorkbook);
       xlWorkbook = null;
       workbooks = null;
       xlApp.Quit();
       Marshal.ReleaseComObject(xlApp);
       xlApp=null;
}

even after releasing resources, still see one excel process

Answer 1

Looks like you're on the right track. However, you're currently its the ExcelApplication that is the actual process you'll see in the Task Monitor and its the ExcelDocument that needs to be closed before the WordApplication can be killed.

Take a look at this simple example:

public void Foo(string filePath)
{
    var application = new Excel.Application();

    // do your stuff here...
    var document = application.Workbooks.Open(filePath, ReadOnly: true, Visible: false);

    // this is the magic:  clear 'application' and 'document',
    // once the work is finished
    if (document != null)
        document.Close(SaveChanges: false);

    if (application != null)
        application.Quit(SaveChanges: false);

    document = null;
    application = null;
}

Here you'll see that we actually properly Close() the workbook (document) and Quit() the application. It is somewhat of what you're doing in the finally section, but remember that the variables are initialised before you can into the try... section.

My advice here: don't call any garbage collection (GC) and Marshal stuff manually. If you're not really sure what it does, it will hurt you later on with side effects. Next, you can further improve on the sample by adding a nice using (...) structure around both the ExcelApplication and the ExcelWorkbook.

Answer 2

There are two methods to achieve this:

First method

You can create a custom ReleaseObject Function like below:

private void ReleaseObject(object obj)
{
    try {
        int intRel = 0;
        do {
            intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        } while (intRel > 0);

    } catch (Exception ex) {

        obj = null;
    } finally {
        GC.Collect();
    }
}

And use the following code order:

xlWorkBook.SaveAs("....");
xlWorkBook.Close();
xlApp.Quit();

ReleaseObject(xlRange);
ReleaseObject(xlWorkSheet);
ReleaseObject(xlWorkBook);
ReleaseObject(xlApp);

Second Method

Create a Class inside the script task, create a Function that get the process id of the Excel.Application and kill it

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Diagnostics;

class Sample
{
    [DllImport("user32.dll")]
    static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);

    Process GetExcelProcess(Excel.Application excelApp)
    {
        int id;
        GetWindowThreadProcessId(excelApp.Hwnd, out id);
        return Process.GetProcessById(id);
    }
}

and then you can use GetExcelProcess(XlApp).Kill();

Answer 3

Properly cleaning up Interop.Excel objects is hard. Do it wrong and you could bog down a SQL or SSIS machine with tons of zombie instances of Excel.

The internet is littered with advice about how to properly Close(),Quit(),ReleaseObject() Interop.Excel objects. The advice that works for you will depend on what order you call your objects AND which methods you call. For instance, if you used a double dot method then you created an uncloseable object. Even if you heed all advice and structure your code to avoid the gotchas, things change when the SSIS package is deployed to the server. Microsoft has basically told developers to not do this.

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

Weird things happen. Also, it doesn't scale. Packages will share the same instance of Excel. One package's cleanup routine can kill an in flight operation of another package.

Listen, I know there are lots of how to's on Interop.Excel and even more how to's of how to clean them up. But, those articles are targeted towards individuals running a single instance of the code on their own machine. It's not advised for an enterprise environment with SSIS. Yes, this stinks.

But, there are solutions! NetOffice is similar to Interop.Excel and helps handles closing and disposing of the COM objects. My strong preference is EPPlus API it is very lightweight, similar to Interop.Excel and 10X faster without the cleanup woes. It can do almost everything Interop can do except printing and macros. Even better than C# would be start start using the built-in SSIS Excel components microsoft has provided to do the ETL work.

Good luck!

Answer 4

Import;

using System.Diagnostics;

To kill zombie excel use this function;

private void KillSpecificExcelFileProcess(string excelFileName)
    {
        var processes = from p in Process.GetProcessesByName("EXCEL")
                        select p;

        foreach (var process in processes)
        {
            if (process.MainWindowTitle == excelFileName)
                process.Kill();
        }
    }

And call the function as follow; (Interop excels are nameless due to we should use (""). This can solve your problem.

KillSpecificExcelFileProcess("");