DTS Designer

The Data Transformation Services (DTS) Designer makes it easy to import, export, and transform heterogeneous data. The DTS Designer provides:

• A transaction-oriented workflow engine that allows you to specify a complex sequence of operations to be performed as part of a DTS package.
• Integrated data movement. The DTS Designer uses a 100 percent OLE DB architecture for copying, validating, and transforming data from the most popular desktop and client/server data sources. Connectivity is provided to Microsoft® Access, Microsoft Excel, Microsoft FoxPro®, Microsoft SQL Server™, dBase, Paradox, Oracle, DB2, and AS/400 data sources.
• An industry-standard method of sharing metadata and data lineage information through the Microsoft Repository.
• The ability to save DTS packages to the Microsoft Repository, SQL Server, or files. Once a package has been created and saved, it can be scheduled for execution using the SQL Server Agent.

The DTS Designer makes is easy to graphically create and edit packages. A DTS package is a self-contained description of all the work that must be performed as part of a transformation. Each package contains one or more steps. Each step executes a task such as executing an SQL statement, transforming data, sending e-mail, or executing a Win32® program or batch file.

The real power of DTS is its extensibility through the Component Object Model (COM). DTS consists of a powerful set of extensible COM objects, making it possible to create your own custom tasks that are fully integrated in the DTS Designer user interface, or to create your own transformations.

Additional DTS Tools

In addition to the DTS Designer, Data Transformation Services (DTS) includes:

• The DTS Import and DTS Export wizards, which are used to import, export, and transform data, and transfer database objects.
• The Data Transformation Services node in the SQL Server Enterprise Manager console tree, which is used to view, create, load, and execute DTS packages.
• The dtswiz and dtsrun command prompt utilities: dtswiz starts the DTS import and export wizards using command prompt options, and dtsrun is used to run a package.

What You Need to Know to Use DTS Designer

To maximize the benefits of using DTS Designer, it is helpful to understand:

• Basic DTS Designer concepts that describe the structure of a package.
• The DTS Designer user interface, which helps you quickly build packages using graphical tools.
• Package management, such as creating, editing, executing, saving, and scheduling packages, and configuring package properties.
• Configuring package components, which includes setting connections, configuring tasks, and defining transformations.
• The process of building and executing a package from start to finish. Several complete examples are included to get you started by stepping you through the process.

See Also

Programming DTS Applications