Home arrow ASP arrow Page 5 - Creating DTS Packages With SQL Server 2000

Creating DTS Packages With SQL Server 2000

Data Transformation Services (DTS) were added to SQL Server 7 and allow us to combine several data-related tasks into one common object. In this article Tim shows us how to create a DTS package with SQL Server 2000 that will access a database and email the results of a query to some sales executives. He also shows us how to execute and error trap DTS packages from within an ASP script.

Author Info:
By: Tim Pabst
Rating: 5 stars5 stars5 stars5 stars5 stars / 327
February 01, 2002
  1. · Creating DTS Packages With SQL Server 2000
  2. · What is a DTS package?
  3. · Creating a DTS package
  4. · Exporting the results set to a text file
  5. · Executing our DTS package in ASP
  6. · Conclusion

print this article

Creating DTS Packages With SQL Server 2000 - Executing our DTS package in ASP
(Page 5 of 6 )

Thanks 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:



<title>Sales Report DTS Package</title>


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


for each objDTSStep in objDTSPackage.Steps

if objDTSStep.ExecutionResult = DTSStepExecResult_Failure then

strResult = strResult & "Package " & objDTSStep.Name & " failed.<br>"

blnSucceeded = false


strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br>"

end if


if blnSucceeded then

Response.Write "<h1>Package Succeeded</h1>"


Response.Write "<h1>Package Failed</h1>"

end if

Response.Write strResult




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:

Executing our DTS package in ASP

Notice that only the tasks are listed here, and not the actual connection object. In our example, the sales email was sent to tim@devarticles.com and mitchell@devarticles.com. 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
blog comments powered by Disqus

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials