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 %>
<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 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.
REFERENCES
For additional information on IIS authentication methods, see the following Microsoft Developer Network (MSDN) Web site:
For information on building components for ASP, see the following MSDN Web site:
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
240221 INF: How To Handle Errors in DTS Package and Step Objects
Keywords: kbinfo KB252987
