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