DTS call from a stored procedure

[netcashin]

I am looking for the simplest way to call a DTS package from
a stored procedure. It is a simple DTS package with no parameters to pass.

 

[foxdev]

You can use xp_cmdshell to invoke DTSRUN.EXE. For the parameters to pass to DTSRUN, schedule the DTS job, then open the SQL Agent's properties for it and look at the command line it uses (it will include the package ID needed). The scheduling is only for the purposes of seeing how SQL Agent handles it. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[netcashin]

Thanks for the advice, but I am getting an error. Here is the code that I used in my stored procedure.

EXEC master..xp_cmdshell 'dtsrun /Skcsql /Ujdoe /Spassword /NDTS_Sophie /H6991EF63-397D-11D5-998C-00E0296DF3E0 /V7991EF64-397D-11D5-998C-00E0296DF3E0 /RChamp'


Here is the error I received. Any ideas?


DTSRun: Loading...

Error: -2147217390 (80041012); Provider Error: 0 (0)

Error string: [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).

Error source:

Help file:

Help context: 0

 

[foxdev]

Does this DTS package run alright when invoked manually?

It looks like an unrelated communications issue. Using the Client Network Utility, set the client machine to use TCP/IP instead of the default Named Pipes and see if that helps. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com