March 20, 2008 at 10:29 am
I'm testing some Excel automation in SSIS, both through the Script Task and the ActiveX Script Task. I'd prefer to use the Script Task because of the .NET environment, but it's behaving differently than what I'm seeing from the ActiveX task.
I create and destroy an Excel Application object in the Script Task, but after the package completes, an instance of Excel.exe is still running in memory (i.e. Task Manager still lists it as a running process). I run basically the same script in ActiveX, and the Excel.exe process is cleaned up as expected.
Has anyone encountered this before, and have any insight about the differences or a solution for this orphaned process?
Below is the two sections of code I'm using. You should be able to just create a new package and cut&paste these into the appropriate tasks. It's currently looking for an Excel worksheet at "C:\book1.xlsx"
It should work fine with older versions of Excel as well.
'====== Script Task Version ===================================
Option Strict Off
Option Explicit On
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim strExcelFile As String
Try
strExcelFile = "C:\book1.xlsx"
xlApp = CreateObject("Excel.Application")
xlBook = xlApp.Workbooks.Open(strExcelFile)
If WorksheetExists(xlBook, "Sheet1") Then
MsgBox("yup")
Else
MsgBox("nope")
End If
Catch e As Exception
MsgBox("ERROR:" & e.ToString, MsgBoxStyle.Critical)
Finally
If Not xlBook Is Nothing Then
xlBook.Close()
xlBook = Nothing
End If
If Not xlApp Is Nothing Then
xlApp.Quit()
xlApp = Nothing
End If
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Function WorksheetExists(ByRef xlWorkbook As Object, ByVal strWorksheetName As String) As Boolean
Dim xlWorksheet As Object
If xlWorkbook Is Nothing Then
WorksheetExists = False
Else
xlWorksheet = xlWorkbook.Sheets(strWorksheetName)
WorksheetExists = Not xlWorksheet Is Nothing
End If
End Function
End Class
'========================================================
'====== ActiveX Task Version ==================================
' VBScript source code
Option Explicit
Sub Main()
Dim xlApp
Dim xlBook
Dim strExcelFile
Dim xlSheet
strExcelFile = "C:\book1.xlsx"
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(strExcelFile)
MsgBox WorksheetExists(xlBook, "sheet1")
xlBook.Close()
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
Function WorksheetExists(ByRef xlWorkbook, ByVal strWorksheetName)
Dim xlWorksheet
If xlWorkbook Is Nothing Then
WorksheetExists = False
Else
Set xlWorksheet = xlWorkbook.Worksheets(strWorksheetName)
WorksheetExists = Not xlWorksheet Is Nothing
End If
End Function
'=========================================================
August 6, 2008 at 5:46 pm
Did you ever figure out the solution to this problem?
August 6, 2008 at 6:14 pm
I'm afraid not. I ended up using the ActiveX task. We'll have to revisit this problem once SQL 2008 is more prevalent since ActiveX will be phased out.
We might look into Excel Services for a more versatile and generic solution.
August 6, 2008 at 7:04 pm
Okay, thanks for the update.
August 8, 2008 at 7:05 am
There is a difference how Script task (VB2005) and AxtiveX script (VB6) handels
creation objects. I have noticed the same behaviour as you desrcibe.
How do you end the Scripttask?
If you put
Dts.TaskResult = Dts.Results.Success
END 'this will close the sub and kill the orphan. Works for me
End Sub
//Gosta
August 8, 2008 at 8:57 am
When I try using the END keyword I get an error message. A google yields the following:
http://msdn.microsoft.com/en-us/library/686w7tx3.aspx
'End' statement cannot be used in class library projects
Updated: November 2007
Class library projects used to create DLLs do not allow the use of the End keyword to stop the execution of code in a procedure.
Error ID: BC30615
To correct this error
Use control structures such as While and For to control the flow of program execution.
See Also
Other Resources
Control Flow in Visual Basic
August 8, 2008 at 1:01 pm
This looks like the right solution but I have not gotten it to work yet.
August 11, 2008 at 3:59 pm
Have you found a solution yet?
There is a lot of suggestions on the net.
This works for me:
etc
' Remember to release the Com object, by decrementing the reference counter.
' If you don't do this, you may end up with very large memory leak because
' of an orphaned Excel process.
If Not xlApp Is Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
End If
' Since there is no Dispose method for Com Objects, if it is not wrapped in
' a disposable class then you will need to Call GC.Collect if you want to
' release excel object immediately. When you don't call GC.Collect here,
' you will notice in the task manager that the Excel process will not exit
' until your application running this code exits, or a garbage collection
' happens.
GC.Collect(0)
End Try
End Sub
August 11, 2008 at 4:42 pm
I am moving towards some VBA in the book itself. The user can choose to reformat and pretty-up the report if desired. It keeps the SSIS task thinner and the server less cluttered. I'm sure that a solution can be worked out, but the time and ongoing complexities don't seem to outweigh the benefits...
August 12, 2008 at 1:24 am
OK
You are doing what I am doing. I dont use SSIS at all.
VBA in Excel works very fine. I use remote SQL to work across the internal net
in some companies to design budget/forcasting Excel applications as a front end
and SQL server 2005 as database.
//Gosta
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply