SSIS format Excel worksheet with script task

Question

SSIS format Excel worksheet with script task

Randy Doub

SSCertifiable

Points: 5322
More actions
May 17, 2013 at 12:31 pm

#272822

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

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

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

Randy Doub SSCertifiable Points: 5322 More actions · Answer 1

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:

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 2

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

😀

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 3

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

Randy Doub SSCertifiable Points: 5322 More actions · Answer 4

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