Microsoft KB Archive/252987

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

Article ID: 252987

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft Active Server Pages 4.0

-



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 %> Q252987 Sample Code

<%   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 & " Step [" & oStep.Name & "] " If oStep.ExecutionResult = DTSStepExecResult_Failure Then sMessage = sMessage & " failed " bStatus = False Else sMessage = sMessage & " succeeded " End If       sMessage = sMessage & "Task """ & oPkg.Tasks(oStep.TaskName).Description & """ " Next If bStatus Then sMessage = sMessage & " Package [" & oPkg.Name & "] succeeded " Else sMessage = sMessage & " Package [" & oPkg.Name & "] failed " End If   Response.Write sMessage Response.Write " Done " %>

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 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.