Run DTS package from stored procedures

Question

Run DTS package from stored procedures

lioi

SSC Journeyman

Points: 84
More actions
April 3, 2002 at 8:51 am

#104890

Can I run a dts package from a stored procedures?
If it's possible, how can I do?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Or if you want to get it with all the parameters schedule it and the disable the job. This may be your better since xp_cmdshell must have the rights to be used. The you do sp_start_job to run it and while it is running it will error if you try to run again on top.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

netguy Mr or Mrs. 500 Points: 546 More actions · Answer 2

Try this I just got through doing the same thing:

EXEC master..xp_cmdshell 'Replace with job script under'

Example:

One way to get the dtsrun command line is to use Enterprise Manager. Right click your DTS package and click 'Schedule Package' click ok on the date/time dialog. Go to SQL Server Agent/Jobs and dbl click the job you just created. Go to steps and the dtsrun for your package will be there, with the encrypted param.

Then you can use it in the xp_cmdshell call, and check BOL for additional param defs.

PS dont forget to delete the job you just scheduled.

Programmers are simple devices, put caffeine in, and get code out.

netguy Mr or Mrs. 500 Points: 546 More actions · Answer 3

Here is a way to run through a web app:

<%@LANGUAGE="VBSCRIPT"%>

<% ' Create Variable for update

Dim dtsID, DTS_SP

dtsID = Request.QueryString("dtsID")

DTS_SP = Request.QueryString("DTS_SP")

%>

<%

set conn = server.createobject("adodb.connection")

conn.open = "dsn=wow;uid=uid;pwd=pas;"

SQL = "UPDATE DTS SET DTS_RUN = '" & Now & "' WHERE dtsID = " + Replace(dtsID, "'", "''") + ""

conn.Execute(SQL)

%>

<%

set CmdExeDTS = Server.CreateObject("ADODB.Command")

CmdExeDTS.ActiveConnection = "dsn=wow;uid=uid;pwd=pas;"

CmdExeDTS.CommandText = "dbo." & DTS_SP + ""

CmdExeDTS.CommandType = 4

CmdExeDTS.CommandTimeout = 0

CmdExeDTS.Prepared = true

CmdExeDTS.Parameters.Append CmdExeDTS.CreateParameter("@RETURN_VALUE", 3, 4)

CmdExeDTS.Execute()

%>

<html>

<head>

<title>ExeDTS</title>

</head>

Executing DTS

Package

Please

Wait. . . . . . . . . . . .

</body>

</html>

Suzanne Corn Newbie Points: 4 More actions · Answer 4

I am not sure why the SP_Start_job or the XP_CmdShell is not working for me. I tried copying the job GUID into the script for both the suggestions mentioned. I can execute the DTS package directly with no problem. I really do not want to use a schedule. I want to execute it on command. When I try the XP_Cmdshell I get errors, and when I do the SP_Start_Job it says it has started but it does not execute. I am fairly new the DBA world so please don't hold back on the explanation. I was thinking that perhaps the scheduled job GUID contained the schedule and therefore would not execute immediately. Are there specific permissions I need to check? Am I correct in saying that the XP...must be executed on the Master Database and the SP_Start_Job must be against the MSDB database? To top all of this off I would like to pass Global variables to the DTS. Right now it is hard coded. Can anyone make any suggestions?