Hello there

I need to build scipt that runs DTS Package on sql server

Is there a way to do this?

You can use xp_cmdshell to call DTSRUN, or you can use the OLE procedures,
e.g.

Data Transformation Services (DTS)
(http://www.databasejournal.com/featu...le.php/1459181)


Or

SQL Agent, create a job and schedule, manually start in Enterprise Manager,
or use sp_start_job.



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

"Roy Goldhammer" <roy (AT) hotmail (DOT) com> wrote

Thankes Darren

If i run the dtsrun using command prompt it works fine

But if i run it using xp_cmdshell it runs it from the server and not from
the client.

I use dts to import data from excel and store it in sql server. The excel
files are always on the client computer

is there a way to cause the xp_cmdshell to run from the client?


"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> ëúá
áäåãòä:OW$udOadFHA.2696 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

Why not invoke the DTS Package from your client code? In principle it's
possible to use COM+ to execute COM objects on a remote machine (the
client in this case) but to do that via xp_cmdshell seems pointless. If
you don't want to run a process on the server then what's the point of
using a proc?

Alternatively, maybe you can share the location where the Excel files
are located so that the package can create them there even when it runs
on the server.

--
David Portas
SQL Server MVP
--

You seem to be confusing two issues. First, where do you want the DTS
package to run? If the file exists at the client then it may be logical
to run the package at the client, which was how I read your previous
reply: "is there a way to cause the xp_cmdshell to run from the
client?". In that case you have no problem, the server doesn't need to
be able to see the file on the client PC, only DTS needs to see it
because DTS runs as a client process.

If you want to run the package at the *server* then the file it is to
import has to be visible to the server. That file could be located
anywhere on the network, including on your client PC. However, you have
said you "will never have the same path from the client and the
server". Does that mean they are on separate networks which are not
trusted? If you can create some location accessible to both client and
server via a UNC path then you could create a process on the client PC
that uploads the file and then calls a stored procedure to execute the
DTS package on the server. Or you could create a process on the server
that watches for a file dropped at a particular location and then runs
the DTS package. The uploading could be done via FTP if you don't have
access via the Windows file system.

--
David Portas
SQL Server MVP
--

Whell David

As i understand from you i must place the excel files on directory shert by
the server

But my enterprise manager is installed on the client computer, so i will
never have the same path from the client and the server.

Is there a way to bypass this problem?

"David Portas" <REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org> wrote