DTS sometime failure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS sometime failure

Author

Topic

GaryMGg
Starting Member

2 Posts

Posted - 2010-03-03 : 15:53:25

I'm having spurious issues with a DTS package in Server 2000.

The DTS package has three steps:
step 1 (ActiveX) creates a Scripting.FileSystemObject to look for a pre-existing file and delete it.
step 2 (SQL) creates the backup using this constructed string:
BACKUP DATABASE @db TO DISK = @backup
step 3 (ActiveX) creates a Scripting.FileSystemObject to move the newly created backup from location A to location B.

When I run the package manually from EM, everything works fine.

When the package runs during the week as a scheduled job without a login,
it typically fails -- but the logs show errors in different places.

Most times, it seems to fail on step 3, moving the file from location A -> B
where B is a UNC path to a shared drive on a raid storage array.
The error log says "Permission denied."
But here's the weird part: Step 1 deletes the file at location B successfully.

I'm an Enterprise Admin.; I have full authority on the database server, the server hosting the raid storage array, the database being backed up, and the shares I'm
writing to.

What code can I add to the package steps to write detailed errors to a log file I can later investigate?

Thanks for any suggestions,
Gary

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-03 : 15:57:00

service account has permissions on the share?