Article ID: 252987 - Last Review: June 29, 2004 - Revision: 3.3

INF: Execute a SQL Server DTS Package from Active Server Pages

View products that this article applies to.

This article was previously published under Q252987

SUMMARY

The SQL Server Data Transformation Services (DTS) packages may need to be executed from remote locations. Using Active Server Pages (ASP), you can execute from remote locations over the intranet or Internet, because ASP is an automation client for the Component Object Model (COM) components and can be executed from the browser.

MORE INFORMATION

To execute a DTS package from ASP code, create an instance of a DTS package object and call the appropriate load method. To execute a local server package, you can use the LoadFromSQLServer method; for example:

<%@Language=VBScript %>
<% Option Explicit %>
<html>
<head>
	<title>Q252987 Sample Code</title>
</head>
<body>

<%
	Const DTSSQLStgFlag_Default = 0
	Const DTSStepExecResult_Failure = 1
	
	Dim oPkg, oStep, sMessage, bStatus
	
	Set oPkg = Server.CreateObject("DTS.Package")
	oPkg.LoadFromSQLServer "MyServer","MyUser","MyPassword",DTSSQLStgFlag_Default,"PackagePassword","","","MyPackage"
	oPkg.Execute()
	
	bStatus = True
	
	For Each oStep In oPkg.Steps
		sMessage = sMessage & "<p> Step [" & oStep.Name & "] "
		If oStep.ExecutionResult = DTSStepExecResult_Failure Then
			sMessage = sMessage & " failed<br>"
			bStatus = False
		Else
			sMessage = sMessage & " succeeded<br>"
		End If
		sMessage = sMessage & "Task """ & oPkg.Tasks(oStep.TaskName).Description & """</p>"
	Next
	
	If bStatus Then
		sMessage = sMessage & "<p>Package [" & oPkg.Name & "] succeeded</p>"
	Else
		sMessage = sMessage & "<p>Package [" & oPkg.Name & "] failed</p>"
	End If
	
	Response.Write sMessage
	Response.Write "<p>Done</p>"
	
%>
</body>
</html>

Using Integrated Windows NT Authentication

You can use the integrated Windows NT authentication feature in SQL Server to access the DTS package. To do this, follow these steps:

Configure the computer that is running Microsoft Internet Information Server (IIS) to use Windows NT authentication.
Configure the computer that is running SQL Server to allow access to all users and groups who need to use the package.

Modify the LoadFromSQLServer method call to use trusted connections, as shown in the following code:

   const DTSSQLStgFlag_UseTrustedConnection = 256
   oPackage.LoadFromSQLServer "MyServer","","",DTSSQLStgFlag_UseTrustedConnection,"","","","MyPackage"

Possible Permissions Issues

When you execute a DTS Package from an ASP page, the package executes in the security context of the Web site visitor, which may be that of the Windows user (if the web site is configured for Basic or NT Authentication) or of the Anonymous account. The package does not execute in the security context of the user ID supplied to load the package from SQL Server. Therefore, it is important to make sure that the account executing the package has appropriate Windows permissions to all resources that the package may use--such as disk files, DLLs, or other drivers (for import/export operations)--especially when the Web site is configured for Anonymous access. If a package works in the DTS designer and works from other client applications, but fails from Web applications, troubleshooting should begin with permissions issues.

For additional information about determining which Windows account will be used to execute the DTS package, click the article number below to view the article in the Microsoft Knowledge Base:

269074 (http://support.microsoft.com/kb/269074/EN-US/ ) INF: How to Run a DTS Package as a Scheduled Job

Using DTS Events

It is not possible to use the events of the DTS package object in the ASP code because noncompiled ASP code does not support events. If you need to use the DTS events, create a compiled ASP component in Microsoft Visual Basic or Microsoft Visual C++, and then invoke the object from an ASP page. The DLL can then be called from the ASP script. This approach also has the advantage of giving access to the GetExecutionErrorInfo method, which is not available in a scripting environment because it uses "by reference" arguments of types other than variant.