Execute a package from Active Server Pages (ASP)

By Darren Green

Page 1 | Page 2

The simplest method is to use the DTS Package Object Library to load and execute the package directly from with ASP:

<%@Language=VBScript %>
<%	' 207
	Option Explicit 
	Response.Buffer = False
%>
<html>
<head>
	<title>SQLDTS.com: ExecASP</title>
</head>
<body>

<%
	Const DTSSQLStgFlag_Default = 0
	Const DTSStepExecResult_Failure = 1
	
	Dim oPkg, oStep, sErr, bSuccess

	Dim sServer, iSecurity, sUID, sPWD, sPkgName, sPkgPWD
	
	' Get Form Variables
	sServer = Request.Form("txtServer")
	iSecurity = CInt("0" & Request.Form("optSecurity"))
	sUID = Request.Form("txtUID")
	sPWD = Request.Form("txtPWD")
	sPkgName = Request.Form("txtPkgName")
	sPkgPWD = Request.Form("txtPkgPWD")
	
	' Validate Variables
	If Len(sServer) = 0 Then sErr = "<li>Invalid Server Name"
	If iSecurity = 0 And Len(sUID) = 0 Then _
		sErr = sErr & "<li>Invalid Username for SQL Security"
	If Len(sPkgName) = 0 Then sErr = sErr & "<li>Invalid Package Name"
	

	If Len(sErr) = 	0 Then
		Response.Write "<p>Executing " & sPkgName & " from " & _
			sServer & "</p>"

		' Load the Package		
		Set oPkg = Server.CreateObject("DTS.Package")
		oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgName
		
		' Execute the Package
		oPkg.Execute
	
		bSuccess = True
	
		' Report Step status
		For Each oStep In oPkg.Steps
			sErr = sErr & "<p> Step [" & oStep.Name & "] "
			If oStep.ExecutionResult = DTSStepExecResult_Failure Then
				sErr = sErr & " failed<br>"
				bSuccess = False
			Else
				sErr = sErr & " succeeded<br>"
			End If
			sErr = sErr & "Task """ & _
				oPkg.Tasks(oStep.TaskName).Description & """</p>"
		Next
	
		If bSuccess Then
			sErr = sErr & "<p>Package [" & oPkg.Name & "] succeeded</p>"
		Else
			sErr = sErr & "<p>Package [" & oPkg.Name & "] failed</p>"
		End If
	End If
	
	' Clean Up
	oPkg.UnInitialize
	Set oStep = Nothing
	Set oPkg = Nothing
		
	Response.Write sErr
	Response.Write "<p>Done</p>"
	
%>
</body>
</html>

This sample uses parameters supplied by a HTML form which is included in the sample download.

Page 1 | Page 2