I created a dts package and it seems to work well when i execute it
from the DTS designer. But when I use the dtsrun utility to run the
same package through a job the package fails. Also, to my surprise the
same dtsrun utility seems to work well when i run it in the command
prompt. It only fails when i make it part of a job. Any idea why this
is happening.
Also some additional info, I am using the sql 2000 tools to create and
edit the package....the server where i am storing this package is sql
7.0.
Has anybody come acros this problem ?

Help in this will be highly appreciated.


Thanks,
Raks

Report this post to a moderator

What SQL 7 service pack level are you running? You need to run SQL 7
SP3 or greater in order for SQL 2000 DTS packages to be compatible,
unless you save the package with a password.

Also, remember that DTS is basically a client application and will run
on the machine you launch it from. If you execute the package on your
PC using EM or DTSRUN, it will run on your PC. When you schedule a job,
it runs on the server. Besides the SP level, the failure on the server
may be due to other configuration differences such as ODBC data sources,
security, etc.


Hope this helps.


Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Raks" <raksql@hotmail.com> wrote in message
news:94871109.0206261929.58a23d67@posting.google.com...
> I created a dts package and it seems to work well when i execute it
> from the DTS designer. But when I use the dtsrun utility to run the
> same package through a job the package fails. Also, to my surprise the
> same dtsrun utility seems to work well when i run it in the command
> prompt. It only fails when i make it part of a job. Any idea why this
> is happening.
> Also some additional info, I am using the sql 2000 tools to create and
> edit the package....the server where i am storing this package is sql
> 7.0.
> Has anybody come acros this problem ?
>
> Help in this will be highly appreciated.
>
>
> Thanks,
> Raks

Report this post to a moderator

When you execute the package through the designer or through dtsrun, are you
physically at the server itself. When executing a package either of these
ways, the dts package executes locally on the machine and uses whatever
environment is active on that machine. Executing the package through a job
will execute on the server in the server's context.

Also, verify that the SQL Server Agent is running using an account with the
appropriate permissions to do what the dts package does as jobs execute with
the privileges of the SQL Server Agent.

I hope that this helps.

Matthew Bando
MBando@WestColebrooke.com


"Raks" <raksql@hotmail.com> wrote in message
news:94871109.0206261929.58a23d67@posting.google.com...
> I created a dts package and it seems to work well when i execute it
> from the DTS designer. But when I use the dtsrun utility to run the
> same package through a job the package fails. Also, to my surprise the
> same dtsrun utility seems to work well when i run it in the command
> prompt. It only fails when i make it part of a job. Any idea why this
> is happening.
> Also some additional info, I am using the sql 2000 tools to create and
> edit the package....the server where i am storing this package is sql
> 7.0.
> Has anybody come acros this problem ?
>
> Help in this will be highly appreciated.
>
>
> Thanks,
> Raks

Report this post to a moderator

Thanks for the info's, these were very helpful. I tried giving a
password and running the package and it seemed to work. Also, the info
about the security context on which the job and the dts package runs
was very helpful. I now understand that the job executes on the
security context of the server while the dts package executed from the
designer executes from context on where it is run.
Thanks once again.

Raks


"Matthew Bando" <mbando@westcolebrooke.com> wrote in message news:<elqmw1dHCHA.2800@tkmsftngp13>...
> When you execute the package through the designer or through dtsrun, are you
> physically at the server itself. When executing a package either of these
> ways, the dts package executes locally on the machine and uses whatever
> environment is active on that machine. Executing the package through a job
> will execute on the server in the server's context.
>
> Also, verify that the SQL Server Agent is running using an account with the
> appropriate permissions to do what the dts package does as jobs execute with
> the privileges of the SQL Server Agent.
>
> I hope that this helps.
>
> Matthew Bando
> MBando@WestColebrooke.com
>
>
> "Raks" <raksql@hotmail.com> wrote in message
> news:94871109.0206261929.58a23d67@posting.google.com...
> > I created a dts package and it seems to work well when i execute it
> > from the DTS designer. But when I use the dtsrun utility to run the
> > same package through a job the package fails. Also, to my surprise the
> > same dtsrun utility seems to work well when i run it in the command
> > prompt. It only fails when i make it part of a job. Any idea why this
> > is happening.
> > Also some additional info, I am using the sql 2000 tools to create and
> > edit the package....the server where i am storing this package is sql
> > 7.0.
> > Has anybody come acros this problem ?
> >
> > Help in this will be highly appreciated.
> >
> >
> > Thanks,
> > Raks

Report this post to a moderator