March 1, 2006 at 8:12 am
I have a fairly simple DTS package. I am pumping data from a SQL query to an Excel spreadsheet.
Next I use ActiveX task to email that spreadsheet (via CDO).
Problem is I get an error when attaching the spreadsheet to the email. The error is:
Error string: Error Code: 0
Error Source= CDO.Message.1
Error Description: The process cannot access the file because it is being used by another process.
I have also tried modifying the flow so that I generate a generic name XLS through the data pump task. I then use an ActiveX task to copy the file to a formatted production name. The next task is the ActiveX email task that sends the formatted name file instead of the generic XLS named file. While this completes with an error, the formatted name file is corrupt and cannot be opened in Excel.
It acts as though the generated XLS is not being "released" by DTS before the other tasks fire (even though they rely on Success workflow). I have even added a task between the copy and the email tasks forcing the system to wait 60 seconds and it still has problems. Any thoughts?
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
March 1, 2006 at 11:50 am
Not that I really think it will make a difference, but have you checked the 'Close Connection on Completion' Checkbox on the Workflow options|options tab?
Also, what about throwing a sleep 10 or something in between the Data Pump task and your mail task, maybe it's just not 100% closed before the next task trys to mail it out?
March 1, 2006 at 1:45 pm
The checkbox is already checked. In addition, I already had the thought of a delay. The entire package right now takes around 13 seconds. I added a SQL Task causing a 60 second delay between the pump and the copy/email. It still had the problem!
Due to changing user requirements, I just made some other modifications to the package. These modifications caused me to have 6 data pumps instead of one (so I could push the data to multiple sheets instead of one where the end user has to sort and/or filter). After that change and the addition of all the Success precedents for the email, it now works.
Go figure. Microsoft at its best!
Brian Glass
Sr. Database Adminstrator
Bombardier Aerospace
March 3, 2006 at 4:22 pm
see if this works....
1. store data into excel sheet with temp name
2. copy the excel file to a file with the correct name via an activex task using fso
3. send the file
May 10, 2007 at 2:35 pm
Thanks this helped an issue I was having.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply