I have a DTS (SQL 2000) which copies a new excel file and dumps data executed from a SP into it. I have used the Activex script task for copying an excel file.
It works fine when executed manually but when scheduled it fails. It deletes the XL file but doesn't copy the file. I think the problem occurs from the line
Set e_app = CreateObject("Excel.Application")
I don't know how to resolve it. Plz help
The code is as below:
Main = DTSTaskExecResult_Success
Set oFSO = CreateObject("Scripting.FileSystemObject")
xtremenw is on the right track. When you run the package manually, you're running it as yourself. When you schedule it, it runs under the SQLAgent user account. Check to see if the SQLAgent account can access both \\AAA\Fold1\ARTA and \\AAA\Fold1\FileCopy.
In the services control panel, does the SQL Server Agent service log on as a user account, or as 'local system'? If it's a user account, make sure that user has permissions to access that folder. If it's 'Local System', then NTAuthority\System would have to have access to that folder, and then, only if it's on the local machine. (I'm assuming it isn't since you're using UNC paths for your files.)
If you need help simply checking permissions on a folder, I suggest you talk with your system or Windows administrator.