Hi,
We have 2 sql 2000 servers
PROD_1
PROD_2

all dts located in msdb on server PROD_2
all dts run from server PROD_1 using xp_cmshell dtsrun

When I start dtsrun from PROD_1 it starts on server PROD1 but connects
and execute
package on PROD_2

dtsrun /S PROD_2 /N 'my_dts_on_prod2'

If I start profler on PROD_2 I can see application "DTS designer"
executing statments defined in dts 'my_dts_on_prod2'

Will it be correct to say
1. Even dts 'my_dts_on_prod2' started from server PROD_1 , dts will
run on server PROD_2
an will use CPU and memory of PROD_2 ,no network traffic (except
execution status )will be generated between PROD_1 and PROD2 servers

2. All above would apply to exection of dtexec (sql2005)

Thank you

Alex

t2581 (AT) hotmail (DOT) com wrote:
DTS runs on the machine which hosts the package. DTSRUN is the host
here, so where does DTSRUN get called? It is the machine which hosts the
SQL Server against wish you issue the xp_cmdshell T-SQL call. SQL Server
is client server, so the T-SQL is always processed by SQL Server.
Shelling out of the main SQL engine with xp_cmdshell does not effect the
machine, it is still that SQL Server engine you have move out of.

Prove where xp_cmdshell is running with something like this-

EXEC xp_cmdshell '@echo %COMPUTERNAME%'


PROD_1 must be the SQL level client, so that is not going to be where
DTSRUN is sat. To run the package on PROD_1, call DTSRUN actually on
PROD_1.


The rule is that DTS alawys runs on the client, or the calling machine,
we say that because it does not run on the SQL Server which is stored
in, which is what most people get confused about. You however have added
an additional level by using T-SQL, which moves the DTS "client" to the
same server as the SQL Server.

This is the same behaviour with SSIS, and DTEXEC.

--
Darren
http://www.sqldts.com
http://www.sqlis.com