Tutorials » Creating DTS Packages With SQL Server 2000
Executing our DTS package in ASPThanks to the way everything in Windows is tightly integrated, we only need a couple of lines to execute our DTS package. We need to instantiate a new DTS package object. The DTS package object is a COM object with the ProgID of "DTS.Package". Once we have instantiated a new DTS package object, we can pass-in our global variable (for the WHERE clause), and execute each step in our DTS package sequentially.
Create a new ASP script called "testdtspkg.asp" and enter the following code into it:
<html>
<head>
<title>Sales Report DTS Package</title>
</head>
<body bgcolor="#FFFFFF">
<%
dim objDTSPackage
dim objDTSStep
dim strResult
dim blnSucceeded
const DTSSQLStgFlag_Default = 0
const DTSStepExecResult_Failure = 1
set objDTSPackage = Server.CreateObject("DTS.Package")
blnSucceeded = true
objDTSPackage.LoadFromSQLServer "(local)",
"sa", "", DTSSQLStgFlag_Default, "",
"", "", "SalesPkg"
objDTSPackage.GlobalVariables("gPaymentTerm").Value
= "Net 30"
objDTSPackage.Execute
for each objDTSStep in objDTSPackage.Steps
if objDTSStep.ExecutionResult = DTSStepExecResult_Failure
then
strResult = strResult & "Package " & objDTSStep.Name
& " failed.<br>"
blnSucceeded = false
else
strResult = strResult & "Package " & objDTSStep.Name
& " succeeded.<br>"
end if
next
if blnSucceeded then
Response.Write "<h1>Package Succeeded</h1>"
else
Response.Write "<h1>Package Failed</h1>"
end if
Response.Write strResult
%>
</body>
</html>
The DTS.Package object has a LoadFromSQLServer method that
lets us load our DTS package. It's signature looks like
this:
LoadFromSQLServer ServerName, Username,
Password, Flags, PackagePassword, PackageGUID, PackageVersionGUID,
Package Name, PersistsHost
As you can see in our example above, several of the parameters are optional. Once we have loaded our package, we have to assign a value to its global variable, gPaymentTerm. Remember that this global variable will replace the "?" in the WHERE clause of our query to the sales and stores tables of the pubs database? We use the GlobalVariables collection to do this:
objDTSPackage.GlobalVariables("gPaymentTerm").Value = "Net 30"
Next, we use the Steps collection of our DTS package object to execute each step in our DTS package sequentially. First step is the OLEDB connection, then the execute SQL task, and lastly the ActiveX script task. For each step, we check whether it succeeded/failed using its ExecutionResult variable and note this in the strResult string variable. If a step fails, then the blnSucceeded variable is set to false.
Lastly, we output whether or not our DTS package succeeded/failed,
as well as the details of each step that it contains. When
I ran the script in my browser, it gave me the following
results:
Notice that only the tasks are listed here, and not the actual connection object. Here's a snippet from the email attachment (c:\salesreport.txt):
Store #7066 sold 50 items with payment type Net 30
Store #7067 sold 40 items with payment type Net 30
Store #7067 sold 20 items with payment type Net 30
