Home arrow ASP arrow Page 2 - 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 - What is a DTS package?
(Page 2 of 6 )

A DTS package is a set of related objects (connections, tasks, and workflows) that can be used to access, transform and manipulate data from a wide range of sources including text files and relational databases. DTS was introduced with the release of SQL Server 7.0 and was carried across to SQL Server 2000 because of its immense popularity.

To create DTS packages, we use the DTS designer (which is accessible through enterprise manager). We will talk more about the DTS designer in a minute, but for now all we need to know is that there are two types of objects that it can create: connections and tasks.

A connection object represents a connection to a data store, such as SQL Server 2000, an Oracle database, or even a text file. They are used to give tasks access to the data they need to transform or manipulate. A task object allows us to work with data accessed through connection objects. Tasks allow us to copy, query, or manipulate data and are generally responsible for providing any sort of activity in a DTS package.

The DTS designer allows us to do more than just access and manipulate data, however. It includes a set of tasks that allow us to transfer files using FTP, send messages to an MSMQ queue, or even create our own ActiveX scripting tasks using VBScript, JScript or PerlScript. By combining all of the connections and tasks that are made available to us through the DTS designer, we can create some fairly complex data-related packages.

Let's take a look at the DTS designer right now. Load Enterprise Manager (Start -> Programs -> Microsoft SQL Server -> Enterprise Manager) and expand both the Microsoft SQL Servers and SQL Server Group nodes in the left column. Next, expand your database node as well as that databases Data Transformation Services node. Enterprise Manager should look something like this:

Expanding nodes to reach the DTS designer

Right click on the Local Packages leaf and choose the New Package option. This will load the DTS package designer. Notice the two sets of objects down the left side of the toolbar? These are the connection and task objects that we will use to create our sample DTS package. The huge area of white space on the right is where the objects that we create as part of our package will go. Click on the icon for any connection or task object to add it to our currently un-saved DTS project. Notice how the object appears in the white space on the right, like this:

Adding objects to a DTS package is easy

Take a look at the options in the menu bar across the top of the DTS designer window. The package menu allows us to save our packages and modify their properties amongst other things. The connection and task menus display exactly the same icons as in the left pane, but they also include descriptions.

The last menu is the workflow menu. The workflow menu contains three options: On Completion, On Success, and On Failure. Each object in our DTS package can respond to these events (which represent the completion of a task, the successful completion of a task, and the unsuccessful completion of a task respectively), and we can optionally use relationships to link our objects to tasks that should be executed when SQL Server fires these events.

Before we move onto creating our sample DTS package, take a look at some of the more important connections and tasks that are available to us through the DTS designer:

  •   Microsoft OLEDB Provider for SQL Server: Allows us to define a connection to any OLEDB compatible data source such as SQL Server 2000, Access, Paradox, dBase or MySQL.
  •   Microsoft Access: Allows us to define a connection to a Microsoft Access database.
  •   Microsoft Excel: Allows us to define a connection to a Microsoft Excel database.
  •   Text File: Allows us to define a connection (either source/destination) to an ASCII text file. Useful when using comma-separated valued flat-file tables.
  •   Microsoft Data Link: Allows us to define a connection to a data source via a UDL (Universal Data Link) file.
  •   ActiveX Script Task: Allows us to use an ActiveX scripting language to interact with other tasks, instantiate COM objects, etc.
  •   Transform Data Task: Allows us to define source-and-destination relationships between connections.
  •   Execute SQL Task: Allows us to build a query against the data source of a connection object. We can then manipulate the results of this query as a recordset through an ActiveX scripting task object.
  •   File Transfer Protocol Task: Allows us to setup a real-time FTP connection to a remote host and work with the files on that host.
  •   Copy SQL Server Objects: Allows us to copy data from one source to another.
Now that we know what DTS packages are and have a good understanding of the types of objects we can use to create them, let's create a simple package of our own.
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