DTS package fails when scheduled but not when run manually

I created a DTS package to collect some data from a remote server.
When I run the package manually, it runs fine, but when I schedule it
to run at night, it always fails.
I added both my own account and my server account to the users on the
remote server, I gave them enough permissions to access the database/
tables I need.
Also, I made sure that the remote server allows both SQL server
accounts and windows accounts, but still no luck.
Anybody have any idea where to look next?
Thanks!
dribibuu@.gmail.com wrote:
> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
You do not need that. The connection to the source and destination SQL
server are stored in the DTS package itself.
What you need is permission to read the DTS package (which is apparently
stored on an SQL server) for the account, under which you execute the DTS.

> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>
|||Hello, If your using intergrated security to log on the other server which it
sounds like you are then you should test the access using that account
directly using enterprise manager or QA. Also check the advanced settings on
the job and ask it to write the output to a file for logging this way you can
get more information on what is going on. also when you schedule a DTS job to
run like this is actually uses xp_cmdshell to execute the dtsrun.exe directly
so make sure you have the correct rights on the proc an NTFS writes on the
file. My suspicion is that if you add the logging to the job and execute the
job you will see what is wrong. did you try manually running the job?
Let me know how this goes.
John Vandervliet
"dribibuu@.gmail.com" wrote:

> I created a DTS package to collect some data from a remote server.
> When I run the package manually, it runs fine, but when I schedule it
> to run at night, it always fails.
> I added both my own account and my server account to the users on the
> remote server, I gave them enough permissions to access the database/
> tables I need.
> Also, I made sure that the remote server allows both SQL server
> accounts and windows accounts, but still no luck.
> Anybody have any idea where to look next?
> Thanks!
>