Excel Continues to run if using variable as new filename

[rawales2]

I am having an issue with Excel in a script. When the script finishes, Excel continues to exist as a running process if (and only if) I use a variable as the saveas file name. If I hard code the file name the process releases just fine (see comment under dim fileName_New line).

Not sure why it would matter but it's driving me CRAZY!!!

Any help is appreciated.

Rob

Script:
Dim fileName_New As String = Dts.Variables("fileName_new").Value.ToString
''''''''''''The above declaration causes Excel to continue to run
''''''''''''the below commented declaration works fine and 'Excel is released
'dim fileName_New as String = "e:\bby\test.xls" '

xlWorkBook_Dest.SaveAs(fileName_New)

''Choose new copy range so that the clipboard won't stop Excel from Closing
lrngCopyRange = wkshtSheet.Range("a1")
lrngCopyRange.Copy()

''Release xlWorkbooks
xlApp.Workbooks.Close()

''''''''''''''SHUT DOWN EXCEL'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

xlApp.Quit()
''Release xlWorkbook COM OBJECTS
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(lrngRow)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(lrngColumn)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(lrngCopyRange)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wkshtSheet)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wkshtSheet2)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook_Dest)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkbook)



Dts.TaskResult = Dts.Results.Success
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp)
xlApp = Nothing

GC.Collect()
GC.WaitForPendingFinalizers()

 

[MrDataGuy]

I have no idea why this issue is happening (will be watching posts by others to learn) but try these code variations to see any help. (This is my keep throwing things against the wall and see what sticks development technique.)

Idea 1:
Since the Variable filename_New is used a write and read once, just plug it into the code directly

 xlWorkBook_Dest.SaveAs(Dts.Variables("fileName_new").Value.ToString)

Idea 2:

Dim Temp_File_Name as String 
Dim File_Name_New as String
Temp_File_Name = Dts.Variable("fileName_new").Value.ToString

‘I noticed that the hard code name was in lower case, could that be it??
File_Name_New = Temp_File_Name.toLower
xlWorkBook_Dest.SaveAs(fileName_New)

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[rawales2]

MrDataGuy- thanks for the response. Unfortunately, neither of those did the trick.

In both cases, the variable used as the file name is in all lower case as well.

I ran it both ways though just to check.

UPDATED INFO: This script is part of an SSIS package and when I ran it from SSIS, it seemed to NOT leave Excel running... not 100% on that testing yet, but I will post back.

 

[MrDataGuy]

What is the data that your DTS is connected to?

It would not be an Excel Spread sheet would it?

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[rawales2]

No, there are no data connections in the package at all. Everything is done from the script.... I know this is not recommended by M$- invoking applications in a script... but it is necessary for this project.

The data sources are dynamic and I have to loop through them, so this was the appropriate methodology.

I don't get it but.... executing the package from the sql server as a job (either direct or as a result of a stored procedure) DOES NOT leave the excel process running. At least not that I can tell- I looked at processes from all users.

So.... long story short it looks like I am good to go.

Thanks for the help!

 

[DaveInIowa]

I wonder if Excel is keeping a reference to the string variable and that is keeping it alive. What would happen if you tried fileName_New = Nothing right after the SaveAs?