Creating DTS Packages with the DTS Object Model

Switch View

DTS Programming (SQL Server 2000)

To create a Data Transformation Services (DTS) package using a programming language that supports COM, you need to create a hierarchy of objects headed by a Package2 object from the DTS object model. After setting the properties of this hierarchy, you can then invoke methods of a Package2 object to run the package. You also can save the package to Microsoft® SQL Server™, a COM-structured storage file, a Microsoft Visual Basic® file, or to SQL Server 2000 Meta Data Services.

Note An object whose name ends with the digit 2 is an extended version of a DTS object from SQL Server version 7.0. If you want to run a package on SQL Server 7.0, it is recommended that you use the corresponding object whose name does not have a 2 appended. For more information, see Extended DTS Objects.

This section summarizes building DTS packages and using DTS objects and features without regard to the programming language you are using for implementation. For more information about configuring a particular development environment and using it to implement DTS packages, see Creating DTS Packages in Visual Basic.

The following table summarizes the topics describing the DTS objects and features you use to implement a package. Most objects and features are optional for a particular package. You are only required to create at least one step and at least one task. You do not need to follow the order provided here except where the DTS object hierarchy dictates. For example, you must create the Package2 object first to gain access to the methods used to create other DTS objects.

Topic	Description
Creating DTS Package Objects and Connections	Create and configure a Package2 object. Create Connection2 objects to access data sources.
Creating DTS Package Workflow and Tasks	Create Step2 objects for the operations the package is to perform. Create PrecedenceConstraint objects to define workflow among the steps. Create a Task object of the type needed for each step.
Adding DTS Transformations	Create Transformation2 objects if needed for tasks that move data between connections.
Adding DTS Column Objects	Assign source and destination Column objects to the transformations if necessary.
Adding DTS Lookups and Global Variables	Create a Lookup object when you need a transformation to look up data in another query rowset. Use GlobalVariable objects to pass data between steps and packages.
Adding DTS ActiveX Scripts	Add a Microsoft ActiveX® script to a step or to a task or transformation that uses scripts.
Adding DTS Query Strings	Add query strings to an object that issues database queries.
Handling DTS Events and Errors	Add handlers for the events of the Package2 object. Add an error handler to a DTS package program.
Managing DTS Package Programs	Execute a DTS package program. Save a DTS package in one of several formats, and load a package from these formats.
Retrieving DTS System, Package, and Log Data	Retrieve information about registered components and DTS packages, and retrieve log data.