DTS package problem

[WKC]

Hi guys, I really have a strange problem that I have no where to look for
answer. I have a DTS that generate a text file with data queries from sql
server 2K then stores it in a local drive. The last step of that DTS is to
copy that file to a network drive using a batch file called by the Process
Task. The commands in the batch file is basically create a network drive,
copies the file over, then delete the source. The file gets copy over when
I manually run the whole package in Enterprise Manager at the DTS designer.
But this DTS package is scheduled to run once a week automatically. It runs
sucessfully during the scheduled time, the file is generated, but it doesn't
copy over to the network drive. I'm not sure why it doesn't do the copying.
In fact, all command in that batch file was executed but the copying part.
I don't think the problem is in the batch file b/c when I run the batch file
alone either by Execute Step or run in command line, the file gets copied
over. So I just don't get it why the file is not copying over when it's
being run at schedule time. Does this has anything to do with security?
Any comments is appreciated. thanks

Wei

[oj]

Re: DTS package problem

Sound like a proxy acct problem. Please see if this article helps:

http://support.microsoft.com/?id=264155

--
-oj

http://www.rac4sql.net

"WKC" <wchen@eibillin g.com> wrote in message
news:Xm_Kb.6476 2$Cs3.8068955@n ews4.srv.hcvlny .cv.net...[color=blue]
> Hi guys, I really have a strange problem that I have no where to look for
> answer. I have a DTS that generate a text file with data queries from sql
> server 2K then stores it in a local drive. The last step of that DTS is to
> copy that file to a network drive using a batch file called by the Process
> Task. The commands in the batch file is basically create a network drive,
> copies the file over, then delete the source. The file gets copy over when
> I manually run the whole package in Enterprise Manager at the DTS designer.
> But this DTS package is scheduled to run once a week automatically. It runs
> sucessfully during the scheduled time, the file is generated, but it doesn't
> copy over to the network drive. I'm not sure why it doesn't do the copying.
> In fact, all command in that batch file was executed but the copying part.
> I don't think the problem is in the batch file b/c when I run the batch file
> alone either by Execute Step or run in command line, the file gets copied
> over. So I just don't get it why the file is not copying over when it's
> being run at schedule time. Does this has anything to do with security?
> Any comments is appreciated. thanks
>
> Wei
>
>[/color]

[mountain man]

Re: DTS package problem

"WKC" <wchen@eibillin g.com> wrote in message
news:Xm_Kb.6476 2$Cs3.8068955@n ews4.srv.hcvlny .cv.net...[color=blue]
> Hi guys, I really have a strange problem that I have no where to look for
> answer. I have a DTS that generate a text file with data queries from[/color]
sql[color=blue]
> server 2K then stores it in a local drive. The last step of that DTS is to
> copy that file to a network drive using a batch file called by the Process
> Task. The commands in the batch file is basically create a network[/color]
drive,[color=blue]
> copies the file over, then delete the source. The file gets copy over[/color]
when[color=blue]
> I manually run the whole package in Enterprise Manager at the DTS[/color]
designer.[color=blue]
> But this DTS package is scheduled to run once a week automatically. It[/color]
runs[color=blue]
> sucessfully during the scheduled time, the file is generated, but it[/color]
doesn't[color=blue]
> copy over to the network drive. I'm not sure why it doesn't do the[/color]
copying.[color=blue]
> In fact, all command in that batch file was executed but the copying part.
> I don't think the problem is in the batch file b/c when I run the batch[/color]
file[color=blue]
> alone either by Execute Step or run in command line, the file gets copied
> over. So I just don't get it why the file is not copying over when it's
> being run at schedule time. Does this has anything to do with security?
> Any comments is appreciated. thanks[/color]


Sounds very much like a security issue: access rights on the remote
machine drive directory need to be granted appropriately to the
service doing the writing. I'd firstly coodinated/check:

the ownership of the DTS step (and task),
the service SQL Agent login account, and
the remote access restrictions (other machine).


Good luck,


Pete Brown
Falls Creek
Oz