locked
Excel object not removing from memory in ssis package RRS feed

  • Question

  • User-2002648249 posted


    Hi,
       I am opeing excel sheet from a ssis package and in this excel some macros run and the excel file closed but the object of excel.exe still remains in memory , i have closed and dispose all excel object .i have used script task for opening excel sheet

    here's script design

      Dim strExcelFile As String
            Dim strMacroName As String
            Dim objExcel As Microsoft.Office.Interop.Excel.Application
            Dim objWB As Excel.Workbook
            Try

                objExcel = CType(CreateObject("Excel.Application"), Excel.Application)
                objExcel.AskToUpdateLinks = False
                strExcelFile = "F:\Tagging_Data_LOV_Wise_Category_Report.xls"
                strMacroName = "VodaFone_LOV_Category_Wise"
                objExcel.Visible = False
                objWB = objExcel.Workbooks.Open(strExcelFile)
                objWB.Application.Run(strMacroName)
                Dts.TaskResult = Dts.Results.Success

                objWB.Close()
                objWB = Nothing
                objExcel.Quit()
                objExcel = Nothing
                System.GC.Collect()
                GC.Collect() : GC.WaitForPendingFinalizers()
                System.GC.Collect()
                GC.Collect() : GC.WaitForPendingFinalizers()

            Catch ex As Exception

                'Raise the error again and the result to failure.
                Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)
                Dts.TaskResult = Dts.Results.Failure
            End Try

        End Sub

    Friday, October 30, 2009 1:12 AM

All replies

  • User-342231188 posted

    If you loop through your excel object code then it will open its multple instances to close all this i normally use this it works fine for me try this

     public static void CloseExcel()
            {
                try
                {
                    if (xlwb != null)
                        UtilityCode.Validation.xlwb.Close(false, false, null);


                    if ((xlapp != null))
                    {

                        for (int i = 1; i <= xlapp.Workbooks.Count; i++)
                        {

                            xlapp.Workbooks[i].Close(false, false , false);
                        }


                        xlapp.Quit();
                    }
                    xlapp = null;

                    GC.Collect();

                    GC.WaitForPendingFinalizers();

                }

    Wednesday, November 11, 2009 1:31 AM
  • User1660962537 posted

    I have the exact same problem. Makes no sense why the connection still remains. Any luck  resolving your issue?

    Thank you!

    Tuesday, February 2, 2010 12:42 PM