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!
>|||"Yura Shalak" <yuras@.nospam.nospam> wrote in message
news:uDb0AchnHHA.4552@.TK2MSFTNGP04.phx.gbl...
> 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.
Hmmm...
I am not sure with the response. The account under which you execute the
job, which in turn executes the DTS, needs to have access to the data you
are trying to access. I'd follow the other response to manually run the job
and see the error message.
Quentin|||Quentin Ran wrote:
> "Yura Shalak" <yuras@.nospam.nospam> wrote in message
> news:uDb0AchnHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> 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.
BTW, how do you schedule this execution?
Is it an OS job or SQL Server Agent job?
>> 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.
> Hmmm...
> I am not sure with the response. The account under which you execute the
> job, which in turn executes the DTS, needs to have access to the data you
> are trying to access.
Nope. Well, not exactly. To be precise, it depends on the connection
properties: if the Windows Authentication is used, then yes, the
permissions are needed. If SQL server authentication is used, then no,
it needs the access to the DTS package only. And the connection
properties for the data processing (including the credentials) are
defined in the DTS package itself.
Of course, if the package works with a local non-SQL data, the account,
which executes the package, needs access to these data. But that is
another story...
>I'd follow the other response to manually run the job
> and see the error message.
Look at the original post: it runs fine when executed manually.|||> BTW, how do you schedule this execution?
> Is it an OS job or SQL Server Agent job?
If you right click the package it gives you the option of scheduling it.
Then it becomes a SQL Agent job.
> Nope. Well, not exactly. To be precise, it depends on the connection
> properties: if the Windows Authentication is used, then yes, the
> permissions are needed. If SQL server authentication is used, then no,
> it needs the access to the DTS package only. And the connection properties
> for the data processing (including the credentials) are defined in the DTS
> package itself.
> Of course, if the package works with a local non-SQL data, the account,
> which executes the package, needs access to these data. But that is
> another story...
Agreed. But it looks that you have quite some SQL Server authenticated
logins, which is not quite good security practice.
> Look at the original post: it runs fine when executed manually.
The package ran fine manually, but not the job executing the package. The
original post did not specify whether the job was manually run.
HTH
Quentin|||Quentin Ran wrote:
>> BTW, how do you schedule this execution?
>> Is it an OS job or SQL Server Agent job?
> If you right click the package it gives you the option of scheduling it.
> Then it becomes a SQL Agent job.
If that is an Agent job and both connections are using Windows
Authentication, then the account under which the Agent is running (not
the SQL Server service) must have access to both remote and local server
data.
Try running the Agent under your account.
>> Nope. Well, not exactly. To be precise, it depends on the connection
>> properties: if the Windows Authentication is used, then yes, the
>> permissions are needed. If SQL server authentication is used, then no,
>> it needs the access to the DTS package only. And the connection properties
>> for the data processing (including the credentials) are defined in the DTS
>> package itself.
>> Of course, if the package works with a local non-SQL data, the account,
>> which executes the package, needs access to these data. But that is
>> another story...
> Agreed. But it looks that you have quite some SQL Server authenticated
> logins, which is not quite good security practice.
Well, Windows security is not always an option.
>> Look at the original post: it runs fine when executed manually.
> The package ran fine manually, but not the job executing the package. The
> original post did not specify whether the job was manually run.
>
Ah... Yes, that is what I missed.