Lesson 3: DTS Programming

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.

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.

• Check for run-time conditions that may affect execution.

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.

• Control the priority of tasks.

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.

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.

• File connections store information about source and destination files.

This includes the file locations, filenames, and the data format.

• Microsoft Data Link connections use a Microsoft Data Link to hold information about source and destination OLE DB data sources.

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.