I assumed I knew the answer but apparently not. When I execute a package from
my w/station (using Enterprise Manager) and open the Task Manager I see the
mmc.exe running at upwards of 97% on my w/station cpu (also causing a
slowdown of other applications on my w/station). I had previously thought
that the vast majority of the work would be done on the SQL Server instance
in which the DTS package resides (my example is a simple pull data from an
ORACLE d/b and push to a network drive). Perhaps I'm misunderstanding what
I'm seeing. Any explanation or alternative suggestions would be appreciated!
TIA.
Chris.

From the Microsoft KB Article:
http://support.microsoft.com/?kbid=269074

Where is the DTS Package Running?

One problem that is frequently reported about DTS packages is the difference
in behavior when a package is run from the SQL Enterprise Manager versus
when the DTS package is scheduled as a job. When you run the package from
the DTS Designer in SQL Enterprise Manager (SEM), the package is running on
the computer where you are seated. If you are at the server (either
physically, or through remote access software), the package is run on the
server. If you are seated at a workstation and you have registered the SQL
Server server in Enterprise Manager, then the package is run on the
workstation. The security context of the package is that of the Windows NT
account you used to log in to that computer. When the package is run as a
scheduled job, the package is always run on the server.

Frequently, a developer creates and tests the DTS package interactively on
their workstation through the DTS Designer in Enterprise Manager. After the
DTS package is debugged, the package is then scheduled as a job. This
changes the location of the package from the developer's workstation to the
server. If the package was loading text data into SQL Server, the package
fails unless the text file and the path to the file exist on the server. If
the package was connecting to another server, the package fails if the
security context of the job does not support the connection.

-- Andrew


"Chris W" <ChrisW (AT) discussions (DOT) microsoft.com> wrote