Usually, a Data Transformation Services (DTS) package includes one or more DTS tasks. Each task defines a work item to be performed as part of the data movement and data transformation process.
Microsoft® SQL Server™ 2000 supplies several DTS tasks that are part of the DTS object model. These tasks can be accessed through DTS Designer (except for the Parallel Data Pump task, which can only be accessed programmatically). You can use them to:
- Transform data. For example, you can use the Transform Data task to copy data, map a wide variety of transformations onto the data, and customize the transformations with a Microsoft ActiveX® script.
- Copy and manage data. For example, you can drop a table, re-create and repopulate the table, and execute a series of queries against the table. Also, you can generate a disconnected Microsoft ActiveX Data Objects (ADO) recordset, which you can then manipulate and access from other steps in the package.
- Run tasks as jobs from within a package. For example, you can use an Execute Process task to run a custom Microsoft Visual Basic® application that collects and aggregates data on a daily basis. Then, you can use an Execute Package task to run a second package that imports and transforms the data generated by the Visual Basic application. You also can use the Send Mail task to send an e-mail to a system administrator if a package step succeeds or fails.
Additionally, you can build your own custom task in a programming language that supports COM (for example, Visual Basic). You can create a user interface for the custom task, including its own icon, if you want to access the custom task in DTS Designer. For more information, see DTS Custom Task.
Adding and Configuring Tasks
A DTS package can contain a single task (for example, an ActiveX Script task that displays a message box when the package is run). However, a package often contains several tasks, connections, and workflow constraints, with each task set to run in the context of an ordered package workflow. You can include multiple tasks of the same type in a package (for example, six Execute SQL tasks), with each task configured differently.
You can add tasks to a package and set their properties in the following ways:
- Graphically, using DTS Designer.
- Programmatically, using the DTS object model to build a package in Visual Basic or Microsoft Visual C++®. For more information, see Programming DTS Applications.
To add a DTS task to a DTS package