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:\copied\excelsheet.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!
KSeriously? *Nobody*has any suggestions on this? I can't believe this has everyone stumped..|||take the line out of active X

objMail.AddAttachment("F:\copied\excelsheet.xls")

are you still getting error? This will tell you if you have an error with the EXCEL file?

If you still get the error there is adifferent problem and will proceed from there.

do you have master..dba_sendmail|||rbackmann,

Thanks for the reply. I know that it is the file attachment method that is causing the error - I'm just not sure how to resolve it.

I have the default MSDN stored proc for sending mail (I called it CDONTS_SendMailAttach so I would remember that it uses CDONTS). I tried doing the above with that instead and it failed with no real error message. I assume if I could get the error from somewhere it would be the same "file is already in use" kind of thing.

I figured doing it this way was better as the errors are a little more verbose and accessible.

I'm still stumped :/

Regards,
K|||i can guess another reason. is your script trying to attach the file while you data pump routine has not yet completed the export? in DTS things run parallel and to put them in sequence you need to add work-flow links between them. try it if you are not already having one in place.|||Upalsen,

Thank you for your reply however I do have workflow links between each task, specifically on success ones. It almost seems like they're being ignored. I have another package which does something similar (though less complex) and it works without error.

Another thing I should mention - if I run each task by itself (by using execute step) the package runs fine - it's only when the package runs each step itself that the error occurs. I'm almost positive it has something to do with the excel interop that occurs when you initiate a connection to an excel workbook.|||I had similar problems when dealing with an instance of EXCEL using VB script connecting trhourg Access. I had to to include a second close of the excwl app.

' save and close workbook -- needs to be done from xlx so Excel knows it is saved
xlx.ActiveWorkbook.Close False
xlx.ActiveWorkbook.Close False
' close Excel object
xlx.Quit

I don't know if this will help but....