Microsoft SQL Server 7 Data Warehousing Technical Support Training Kit
DTS provides COM interfaces that expose the functionality to create and execute packages. You can use these interfaces to create custom applications that use DTS services to create packages or execute existing packages. The DTS user interface applications are built on top of these COM interfaces. In this lesson, you will learn about the basics of using the DTS object models.
After this lesson, you will be able to:
- Describe the basic architecture of DTS
- Describe the objects used to make a DTS package
Estimated lesson time: 30 minutes
DTS Object Model
The DTS object model contains interfaces, objects, collections, methods, and properties. Groups of objects are hierarchically arranged to define DTS package elements such as steps, tasks, connections, and global variables. Objects defined using the DTS object model embody the metadata of the packages. The DTS object model makes these DTS objects available to programmers. If this metadata is stored in Microsoft Repository, it is available to any tool or program that can use the Microsoft Repository.
Conceptually, the DTS object model uses groups of objects to define the basic elements of a DTS package. The object model addresses these basic elements in a hierarchical structure in which elements contain other elements in a tree, which has the Package object at the root of the tree.
A basic understanding of DTS programming begins with an overview of DTS architecture, object hierarchy, supporting files, installation information, and a discussion of programming platforms.
Programming through Extensible Interfaces
DTS allows you to program transformations through two different interfaces. In the COM object browser in your development tool, there is an OLE Automation package interface, called the Microsoft DTSPackage Object Library (DTS) that is implemented by Dtspkg.dll. Programmers can create and modify DTS packages through the OLE Automation interface using any language that supports OLE Automation. There is an additional interface used by ActiveX scripting tasks called Microsoft DTSDataPump Scripting Object Library (DTSPump) that is implemented in Dtspump.dll. Programmers can write directly to the data pump through a COM interface using C or C++. Both interfaces, the package and pump, are extensible. There are many collections and objects in these interfaces. The following sections describe some of the important ones.
The Package Object
The Package object is the root object from which all other objects stem. To define tasks, transformations, and steps, you must first create a Package object. You can create new Package objects or load existing packages from SQL Server, the Microsoft Repository, or a file.
The Steps Collection
The Steps collection is a group of Step objects that contain information about the flow and execution of tasks within a DTS package.
The PrecedenceConstraints Collection
The PrecedenceConstraints collection is a group of PrecedenceConstraint objects containing information about the order in which steps are executed. Each step has a PrecedenceConstraints collection of zero or more PrecedenceConstraint objects that determine when the step may execute.
The Tasks Collection
The Tasks collection is a group of Task objects that contain information about units of work to be performed as part of the transformation process. The Tasks collection contains all of the defined tasks in a DTS package.
The Connections Collection
The Connections collection is a group of Connection objects containing information about connections to OLE DB service providers. This collection allows connection pooling and reuse across steps and tasks in a package.
The GlobalVariables Collection
The GlobalVariables collection is a group of GlobalVariable objects containing information about variables that allow data to be shared across steps and ActiveX scripts. The GlobalVariables collection is dynamic; values may be added to the collection as the package executes.
DTS Package Object Internals
A package is made up of four collections of objects:
- Task objects
- Step objects
- Connection objects
- GlobalVariable objects
These objects work together to ensure that a DTS package can perform its transformation duties.
Task Objects
DTS packages contain one or more Task objects. These Task objects
- Define the work to be done by the package
- Execute Transact-SQL statements
- Move and transform data using the DTS data pump
- Execute ActiveX scripts
- Run external programs
- Run other packages
Step Objects
Each Task object must have an associated Step object or it will not be executed. Step objects enable you to do the following:
- Control the flow of execution of the tasks in your DTS package.
- Check for run-time conditions that may affect execution.
- Control the priority of tasks.
Step objects control the flow of the tasks by having dependencies on the completion of other tasks. These dependencies are determined by the Step objects PrecedenceConstraints collection. If a Step object does not have any precedence constraints, it may be executed immediately.
Multiple Step objects can execute in parallel for enhanced performance. Run-time conditions can be checked because previous steps can return values as well as receive return codes from ActiveX scripts.
Package threads normally run at the same priority as the package. You can assign various priorities to individual threads by setting properties of the Step object.
Connection Objects
Packages contain zero or more Connection objects. The data pump uses Connection objects to read or write data. There are three types of Connection object:
- Data source connections hold information about source and destination OLE DB data sources. ODBC data sources are supported by the Microsoft OLE DB Provider for ODBC.
- File connections store information about source and destination files.
- Microsoft Data Link connections use a Microsoft Data Link to hold information about source and destination OLE DB data sources.
The information stored includes server names, usernames, passwords, and the format of the data to be retrieved. The connection is established the first time it is used by a task.
This includes the file locations, filenames, and the data format.
A Microsoft Data Link is a file with a .UDL extension that stores OLE DB connection information. A Microsoft Data Link is similar to an ODBC file DSN.
NOTE
Some tasks do not require connections, so you may create packages that do not have any connections. The Active Script, Execute Process, Transfer SQL Server Objects, Send Mail, and OLAP Services Processing tasks do not require connections.
GlobalVariable Objects
A GlobalVariable object contains information about a variant variable. This information makes it possible to share data across steps and ActiveX scripts during the execution of a package.
Transacting DTS Packages
Packages can participate in transactions. The package itself must support transactions, and each step object can be included in the transaction, as needed.
Configure the Package to Support Transactions
Once a package is created, it can be set to support transactions. This causes the Package object to create a transaction for all tasks in the package.
Designate Which Step Objects Should Participate in the Transaction
If only certain steps within the package need to be transacted, you can indicate this by setting the transaction property for those steps only rather than setting the transaction at the package level.
DTS Programming Samples
You can find DTS sample programs written in Visual Basic and Visual C++ on the Microsoft SQL Server 7.0 CD-ROM. The samples are located in the \devtools\samples\DTS folder.
Lesson Summary
DTS provides a full object model that makes it possible to write custom DTS programs with any language that supports COM automation. The Package object is the root object of the object hierarchy from which all other objects, such as Steps, Tasks, and Connections, are available. All or only some of the tasks in a package can be transacted.