Excel Locked File DTS Woes

Hello All,

Hopefully one of you gurus can offer a solution to this problem:

First off I do not have the ability to use SQLMail - so that is not an option.

I have a DTS package that performs the following steps in order to populate an excel sheet with some data and email it off to a coworker:

1) FTP Task to copy excel sheet template from one folder to another on same machine

2) Data pump between SQL server connection (local) and copied excel sheet.

3) SQL script task which truncates a table on the sql server

4) activeX script task which contains the following code that uses CDO to attach the excel sheet that was just populated to an outgoing email:


Function Main()

Dim iMsg

set iMsg = CreateObject("CDO.Message")

Dim objMail

Set objMail = CreateObject("CDO.Message")

objMail.From = "from@from.com"

objMail.To = "to@to.com"

objMail.AddAttachment("F:copiedexcelsheet.xls")

objMail.Subject="subject"

objMail.HTMLBody = "Body"

objMail.Send

Set objMail = nothing

Main = DTSTaskExecResult_Success

End Function


The result of DTS execution is a failure on the activeX step:

Error Description: The process cannot access the file because it is being used by another process.


I assume this means Excel still has its grubby paws on the file - but I've tried alleviating this by (1) using a waitfor delay between the excel connection and the activex script task and even by (2) using the run package task to run a separate package that only has the activex mailsender script in it (thinking the parent package would terminate along with the excel process before running the next package - maybe I'm wrong here?).

Can anyone suggest a solution to this!?

Thanks!
K