DTS Permissions problem

[waddies]

I have a DTS job which has 2 steps.

Step1. Generates an Excel spreadsheet on the server.
Step2. Tries to rename the Excel file to something else using the following VBScript code in an ActiveX.


sSourceFile = "\\servername\foldername\filename.XLS"
sSourceFile2 = "\\servername\foldername\NEWfilename.XLS"

Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FileExists(sSourceFile) Then
oFSO.movefile sSourceFile, sSourceFile2
End If

Set oFSO = nothing

Note: This is all done on the same server so no worries about permissions across servers.

I seem to be getting the following error message when I run this DTS

"Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Permission denied Error on Line 36"

Line 36 is the following line of code
oFSO.movefile sSourceFile, sSourceFile2

What I think is the problem is that the Excel file is still lock by step1 in the DTS, when step2 tries to rename it.
What I need to know is how do I release the lock on the Excel file before running step2?

Or am I barking up the wrong tree completely.

Thanks in anticapation
Waddies

 

[pkav]

Hi,

I'm not going to offer much hope to you, but I'd suggest you recheck the permissions for your server.

I created a very simple DTS package with a transform data task followed by yuor active-X code snippet and it seemed fine when writing to a local dirve.

If you know the name of the destination excel file up front, could you use dynamic properties to change the output filename?