New to DTS, and finding need for programmatic control of package
execution. I found the DTS objects, and see the Execute on the Package
object. BOL does not make clear if the package actually runs from my
workstation, or from the SQL SERVER computer. I would like to
understand the context of the process under which the package executes
so that I know how disk file names will be resolved. I would like for
packages to execute at the server.

TIA - Best regards, Lee Gillie, Spokane, WA

Hi Lee,

Package execution occurs on the machine that the package is invoked on.
If you need the execution to occur on the server, then you can schedule a
job on SQL Server to run the package. What you bring up is usually a
difficult thing for people to get past when developing DTS packages
(creating packages that dynamically determine file locations based on where
they are run at). Just as you've already brought up, resolution of file
locations occurs at where ever the package is run. So packages sometimes
have problems if for example you create a DTS package on the server, access
and run the package from Enterprise Manager on your workstation. Or maybe
the opposite occurs where you develop the package at your workstation, put
it into production, and now the package can't find your C:\Testing
directory. HTH.

--
Regards,

Don R. Watters
Data Group Manager
PhotoWorks, Inc.


"Lee Gillie" <ANTISPAMIFICATION_lee (AT) odp (DOT) com> wrote

Lee Gillie wrote:
IMHO the best (because easiest) solution for execute packages on a server is
to create a job for them and start the job via sql-command "sp_start_job".
After starting the job you can check the job-status via sql-command
"sp_help_job". Another reason for me to start the DTS-packages via jobs is
that jobs ALWAYS running under the account of the serveragent-service. It's
very easy to start a job via ASP-Pages and so on.....
There's no need for me to catch any (error)-events coming from within the
package itself.

Any other suggests?

regards

frank
bremen(bextown) / germany

A slight correction. If the job owner is a member of the sysadmin role
then yes the SQL Server Agent service account fires the package. If not
then it is the proxy account that handles this

http://support.microsoft.com/?kbid=269074

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Frank Matthiesen" <fm (AT) xax (DOT) de> wrote

In article <br29ik$27sarb$1 (AT) ID-151790 (DOT) news.uni-berlin.de>, Frank
Matthiesen <fm (AT) xax (DOT) de> writes
Frank,

I think the statement "jobs ALWAYS running under the account of the
serveragent-service" is wrong.
From Books Online (sp_start_job)-

When sp_start_job is invoked by a user who is a member of the sysadmin
fixed server role, sp_start_job will be executed under the security
context in which the SQL Server service is running. When the user is not
a member of the sysadmin fixed server role, sp_start_job will
impersonate the SQL Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account.


So for my situations I cannot use sp_start_job from ASP because there is
no way I will allow a web server (ASP) to have a sysadmin account to my
SQL Server. The ASP application will have minimal privileges to connect
to my SQL Server.

A simple workaround is to use a work table. The ASP inserts a record
into a work table indicating that the package should be executed. A
regularly running job on SQL Server can check the work table and start
the package if required.

Lots of variations on this theme but you get the idea.

I also think is sometimes appropriate to execute the package on the web
server, but due to the very limited permissions of IWAM/ISR accounts a
COM+ object wrapper with a set identity is often useful to abstract and
potentially elevate the security context of the package.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren Green wrote:
Yes....y're right

regards

frank