Packages

The fundamental element of a DTS application is the package. You can create a package in one of two ways: via the DTS Import/Export Wizard or the DTS Designer. A package can be stored in SQL Server, in the Metadata Repository, as Visual Basic code (this is a one-way operation—you cannot load packages saved as VB into the DTS Designer), or in a COM structured storage file.

When you choose the SQL Server storage option, the DTS Designer calls an undocumented stored procedure named sp_add_dtspackage to store the package in msdb. The package is passed as an image data type to the procedure and stored in a table named sysdtspackages. You can view the list of packages saved to sysdtspackages via the Local Packages node located under the Data Transformation Services node in Enterprise Manager.

COM structured storage files resemble file systems in many ways. They provide a means of persisting COM objects to disk. Storing a DTS package on disk as a COM structured storage file allows you to easily transport it via other mediums such as e-mail and CD-ROM.

A DTS package consists of Connection objects (references to OLE DB providers), tasks, transformations, steps, and precedence constraints. The precedence constraints control the package's workflow—the flow of data through it. (You can also control package workflow using ActiveX scripts, as we'll discuss in just a moment.) The transformations specify whether and how data is converted and formatted as it is transferred from a source to a destination. Connection objects provide the ends of a transformation process—the sender and the receiver of the data being transformed and transferred from one location to another.

Connections

The connections supported by DTS consist of OLE DB data sources. Because an OLE DB provider exists that supports ODBC data sources, you can also use ODBC data sources with DTS. Examples of the kinds of data you can access with DTS through OLE DB include the following.

• SQL Server databases (via SQLOLEDB, the native SQL Server OLE DB provider)

• Other RDBMS backends such as Oracle, Sybase, and DB2

• Nonrelational stores such as Active Directory, Indexing Service, Site Server, and Exchange Server

• Text files—delimited as well as fixed-format

• HTML

• Access, Excel, and Visual FoxPro

• dBase and Paradox

• Other backends accessible via ODBC

Tasks

A task is an atomic work item. Each task in a DTS package defines a part of the data transformation process and is executed as a single step. Out of the box, DTS provides a number of task objects you can use to build complex data transformation applications. Examples of the types of things you can do include the following.

• Data copying between disparate OLE DB providers. You can load data from one type of data source into another type of destination. For example, you can copy data from an Oracle database into a SQL Server database or vice versa. You can transfer nonrelational as well as relational data, and you can use DTS's Bulk Load functionality to set up high-speed data loads from text files into SQL Server databases.

• Complex data transformations. You can map columns from a data source to a data destination and specify how the data is to be transformed as it is copied. You can use ActiveX scripts to manipulate the data in transit, and you can specify one-to-many, many-to-one, and other unusual relationships between source and destination data.

• Nested packages. Using the Execute Package task, you can set up one package to call another, adjust its global variables, and so on.

• Message transmittal. DTS provides facilities for sending e-mail based on package step completion status and for interacting with Message Queue in order to transmit messages between packages.

• T-SQL and ActiveX script execution. You can execute T-SQL and ActiveX scripts of your choosing as package steps. The T-SQL statements you execute can be ad hoc queries as well as stored procedure calls and can be generated using the DTS Query Designer. The ActiveX scripts you build can be any ActiveX script language. VBScript and JScript are available by default on any SQL Server installation.

• Database object duplication. You can transfer tables, views, stored procedures, user-defined functions, defaults, rules, and user-defined data types from one SQL Server database to another. DTS can even optionally script the entire operation for you as a series of T-SQL scripts and BCP data files.