SSIS format Excel worksheet with script task

  • I'm exporting to an Excel file and then formatting the spreadsheet. Using SQL2008R2. When I run the package from the designer it runs successfully and formats the spreadsheet correctly.

    But after execution, I still see Excel running in background.

    I have an application.Quit(). Am I doing something wrong?

    Here's my script Task code:

    Public Sub Main()

    '

    ' Add your code here

    Dim oBook As Object

    Dim oSheet As Object

    Dim oXLS As Object

    Dim oFile As Object

    oFile = "\\MyHouse\MyFilePath\MyFile.xls"

    oXLS = CreateObject("Excel.Application")

    With oXLS

    oBook = .Workbooks.Open(oFile)

    End With

    oSheet = oBook.Worksheets("KHS_Case_Master")

    With oSheet

    .Range("KHS_Case_Master").Font.Name = "Arial"

    .Range("KHS_Case_Master").Font.Size = 10

    .Range("A1", "FZ1").Font.Bold = True

    End With

    oSheet.Activate()

    With oBook

    .save()

    End With

    oXLS.Quit()

    oXLS = Nothing

    oBook = Nothing

    oSheet = Nothing

    oFile = Nothing

    '

    Dts.TaskResult = ScriptResults.Success

    End Sub

  • This must be all about me having a 64-bit client and running 64-bit Office 2010. I only leave Excel running in the background when I execute the package from the designer on my client.

    The package runs successfully and does not leave Excel running when I run it as a scheduled job on the SQL Server it is destined for. FYI - I installed 32-bit Excel on the SQL Server even though its OS is Win2008R2. I also had to create folder C:\Windows\SysWOW64\config\systemprofile\Desktop to the SQL Server (one of the oddest fixes I've ever seen).

    Excel destinations are so uncooperative. :angry:

  • Randy

    You are not the only one having the described problem "by design" from Microsoft.

    One work around is to add a small scriptpart which kills the process after quit.

    Process.GetCurrentProcess()

    processes = Process.GetProcessesByName("Excel")

    For Each myproc In Process.GetProcesses

    If myproc.MainWindowTitle.Contains("Excel") Then

    myproc.Kill()

    End If

    Best luck

    😀

  • I forgott to say that my script is VB.net

    This is an alternative solution

    processes = Process.GetProcessesByName("Excel")

    For Each proc In processes

    If processes.Length <> 0 Then

    proc.Kill()

    End If

    Next

  • Those script weren't finding any Excel processes.

    But I tried this and it works:

    For Each p As Process In Process.GetProcesses

    If p.ProcessName = "EXCEL" Then

    p.Kill()

    End If

    Next

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply