Many organizations centralize data to improve corporate decision making. However, this data often is stored in a large variety of formats on a number of different systems. By using DTS, you can import, export, and transform data among multiple homogeneous or heterogeneous sources and destinations using an OLE DB_based architecture. This lesson introduces you to DTS and describes how to create a DTS package.
After this lesson, you will be able to
- Create a DTS package with the DTS Import and DTS Export Wizards
Estimated lesson time: 60 minutes
DTS is able to import, export, and transform data between SQL Server and any OLE DB,
DTS can be used with any OLE DB data source and destination; you are not required to use SQL Server 7 for either source or destination. This makes DTS a general-purpose data transfer and transformation tool with a wide range of applications.
DTS moves table schema and data only between heterogeneous data sources. Triggers, stored procedures, rules, defaults, constraints, and
user-defined data types can be transferred only if the source and destination are both SQL Server 7.
The process of transferring data is an integral part of all database management systems. DTS provides an extensible Component Object Model (COM)_based architecture that allows customers, independent software
With DTS, users create and execute a DTS package, which completely describes all of the work that is performed as part of the transfer and transformation process.
A DTS package defines one or more data transformation steps. Steps are executed in a coordinated sequence, which you can control. Each step can perform a different type of operation. For example, step 1 might copy and transform data from an OLE DB source to an OLE DB destination by using the DTS Data Pump, step 2 might execute a script, and step 3 might load and execute an external program (.EXE) or even a batch file (.CMD or .BAT). Figure 6.1 illustrates such a DTS package.
Figure 6.1 An example of a DTS package
DTS packages are self-contained and can be executed from SQL Server Enterprise Manager or by using the dtsrun utility. DTS packages can be stored in the msdb database in SQL Server, linked to the Microsoft Repository, or saved as COM-structured storage files. These options and their implications are described in Lesson 3.
When you use DTS, the data source and destination can be heterogeneous. Using SQL Server as a data source or destination is not required. DTS may simply be the mechanism that transfers data between two data sources.
DTS uses OLE DB providers to import, export, and transform data. Using OLE DB allows access to a wide variety of data source and destination types. OLE DB is a COM interface_based data access mechanism. It can access any data storage format (databases, spreadsheets, text files, and so on) for which an OLE DB provider is available. An OLE DB provider is a software component that exposes an OLE DB interface. Each OLE DB provider is specific to a particular storage mechanism, such as SQL Server databases, Microsoft Access databases, or Microsoft Excel spreadsheets.
OLE DB is an evolutionary extension of ODBC. ODBC is limited to SQL-based relational databases; OLE DB provides access to any data format. OLE DB providers are conceptually the same as ODBC drivers. The OLE DB provider for ODBC makes it possible to use OLE DB applications, such as DTS, with any data source for which you have an ODBC driver.
The following table describes the OLE DB providers available with SQL Server. Other providers are available from third-party vendors.
|Data source or data destination||Description|
|Native OLE DB||Accesses applications such as SQL Server, Excel, and Access, as well as workgroup and enterprise databases|
|ODBC||Accesses Oracle, Access, and DB2 by using the OLE DB provider for ODBC|
|ASCII text files||Access ASCII fixed-field-length text files and ASCII delimited text files by using the SQL Server DTS Flat File OLE DB provider|
|Customized||Supports third-party and ISV OLE DB providers|
Using DTS steps, it is also possible to create packages that do such things as performing high-speed nonlogged
The DTS Data Pump is an OLE DB service provider that provides the infrastructure to import, export, and transform data between heterogeneous data stores. It is a high-speed, in-process COM server that moves and transforms OLE DB rowsets. The DTS Data Pump uses OLE DB because OLE DB provides access to the broadest possible range of relational and nonrelational data stores.
The DTS Data Pump provides the extensible COM-based architecture that allows complex data validations and transformations as the data moves from the source to the destination. The Data Pump exposes the source and destination OLE DB rowsets to scripting languages, such as VBScript, Microsoft JScript, and PerlScript, in a DTS package. This ability allows the expression of complex procedural logic as simple, reusable ActiveX scripts. Scripts can validate, convert, or transform column values as they move from the source through the Data Pump to the destination.
DTS tools include the DTS Import Wizard, the DTS Export Wizard, DTS Designer, the dtswiz and dtsrun
The DTS Import and DTS Export Wizards offer many ways to customize or simplify the method in which data is
When copying a table, the DTS wizards by default do not copy indexes, triggers, or constraints. If the table is to be created by the package, you can manually edit the Transact-SQL that is used to create the table and add the statements needed to create indexes, triggers, or constraints.
You can start the DTS Import Wizard and the DTS Export Wizard from SQL Server Enterprise Manager, from the Microsoft SQL Server 7 program
The DTS Import Wizard and the DTS Export Wizard are the same utility. You can move data into or out of SQL Server or any other OLE DB data source using either wizard. The text in the title bar of the utility changes depending on which wizard you select.
In this exercise, you will import summary data into a new table using the DTS Import Wizard.
In the following steps, if an option is not specified, accept the default.
SELECT ProductName, SUM(o.UnitPrice * Quantity) AS Total FROM [Order Details] INNER JOIN Products p ON o.ProductID = p.ProductID GROUP BY ProductName
If you don't want to type the query, you can click Browse and open C:\Sqladmin\Exercise\Ch06\Query.sql.
SELECT * FROM StudyNwind..ProductTotals
In this exercise, you will export data using the DTS Export Wizard and save the DTS package. The DTS package will copy a list of South American customers into a delimited text file.
In the following steps, if an option is not specified, accept the default.
In this exercise, you will review the job schedule that was created to execute your DTS package.
DTS Designer is a graphical DTS package editor. The work surface includes a toolbar and an extensible tool palette that you can use to add package objects and specify workflow.
When you create a new DTS package from the console tree, the DTS Designer work surface opens in a new Microsoft Management Console (MMC) window. Two tool palettes contain icons for transformation tasks and data connections.
DTS is a general-purpose data transfer and transformation tool with a wide range of applications. It provides the ability to copy table schema and data between DBMSs, create custom transformation objects, access applications using third-party OLE DB providers, and build data warehouses and data marts in SQL Server. DTS can be used with any OLE DB data source and destination; you are not required to use SQL Server 7 for either source or destination.
DTS tools include the DTS Import Wizard, the DTS Export Wizard, DTS Designer, the dtswiz and dtsrun command-prompt utilities, and the Data Transformation Services node in the SQL Server Enterprise Manager console tree.