Lesson 2: Introduction to Data Transformation Services | Microsoft SQL Server 7.0 System Administration Training Kit

[Previous] [Next]

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

Overview of DTS

DTS is able to import, export, and transform data between SQL Server and any OLE DB, Open Database Connectivity (ODBC), or text file format. When you use DTS, you can

Copy table schema and data between database management systems (DBMSs).

Create custom transformation objects that can be integrated into third-party products.

Build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis.

Access applications using third-party OLE DB providers. This allows applications for which an OLE DB provider exists to be used as sources and destinations of data.

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.

NOTE
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 DTS Process

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 vendors (ISVs), and consultants to create new OLE DB data sources and destinations, tasks, and transformations.

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.

The DTS Package

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.

click to view at full size.

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.

The DTS Data Source and Destination

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.

NOTE
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 inserts (using bcp or BULK INSERT), transforming and publishing data as HTML, or exporting data to pivot tables in Excel.

The DTS Data Pump

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

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.

The DTS Import and DTS Export Wizards

The DTS Import and DTS Export Wizards offer many ways to customize or simplify the method in which data is copied from source to destination. With DTS wizards, you can

Define DTS packages in an easy-to-use, interactive user interface. The result of using the wizard is a package that you can save and edit directly with DTS Designer if you want to.

Copy data between heterogeneous data sources.

Schedule DTS packages for later execution.

Copy an entire table or the results of a SQL query, such as a query that involves joins of multiple tables or even distributed queries. The Query Builder within the wizard allows users who are inexperienced with the SQL language to build queries interactively.

TIP
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.

Copy all of the objects from one SQL Server 7 database to another.

You can start the DTS Import Wizard and the DTS Export Wizard from SQL Server Enterprise Manager, from the Microsoft SQL Server 7 program group on the Start menu, or by using the dtswiz command-prompt utility.

NOTE
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.

Exercise: Importing Data with the DTS Import Wizard

In this exercise, you will import summary data into a new table using the DTS Import Wizard.

To import data from a SQL query

Right-click your server, point to All Tasks, and then click Import Data. This launches the Data Transformation Services Wizard.

Click Next.

In the following steps, if an option is not specified, accept the default.

In Source, select Microsoft OLE DB Provider For SQL Server.

In Server, select (local).

Select Use Windows NT Authentication.

In Database, select StudyNwind. Click Next.

In Destination, select Microsoft OLE DB Provider For SQL Server.

In Server, select (local).

Select Use Windows NT Authentication.

In Database, select StudyNwind. Click Next.

Select Use A Query To Specify The Data To Transfer. Click Next.

In Query Statement, type

 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.

Click Parse. If you have typed the statement correctly, you see the following confirmation message The SQL statement is valid Click OK to close the message. Click Next.

In the Table(s) list, click the value in the Destination Table column (the default value is Results). Since you are creating a new destination table, you cannot select its name from the drop-down list. Type in the name of the new table: ProductTotals.

Click the ellipsis button in the Transform column.

Check Drop And Recreate Destination Table. Uncheck Nullable for Total under Mappings. Click OK to close the Column Mappings and Transformations dialog box. Click Next.

Check only Run Immediately in the When section.

Check Save DTS Package and select SQL Server in the Save section. Click Next.

In Name, type StudyNwind Product Totals. In Description, type Year to date product totals.

For Server Name, select (local), select Use Windows NT Authentication, and then click Next.

Click Finish. The Transferring Data dialog box indicates the progress of the data transfer. An error will occur on the Drop Table ProductTotals Step. This is expected, as the table does not already exist. It will not affect the data transfer.

A dialog box indicates when the transfer has completed successfully. Click OK to close the dialog box, and click Done to close the Transferring Data dialog box.

Expand your server, expand Data Transformation Services, and click the Local Packages icon. Note that your new DTS package is listed in the details pane.

Open SQL Server Query Analyzer.

To view the imported results in the ProductTotals table, execute the following Transact-SQL statement.

 SELECT * FROM StudyNwind..ProductTotals

Exercise: Exporting Data with the DTS Export Wizard

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.

To export data by using the DTS Export Wizard

Right-click your server, point to All Tasks, and then click Export Data. This launches the Data Transformation Services Wizard.

Click Next.

In the following steps, if an option is not specified, accept the default.

In Source, select Microsoft OLE DB Provider For SQL Server.

In Server, select (local).

Select Use Windows NT Authentication.

In Database, select StudyNwind. Click Next.

In Destination, select Text File.

In File Name, type C:\Sqladmin\Exercise\Ch06\Sacust.txt. Click Next.

Select Use A Query To Specify The Data To Transfer. Click Next.

Click Query Builder. Click Customers, and then click > to add all columns from the Customers table to the Selected Columns list.

In the Selected Columns list, click on Phone and click < to remove it from the list. Do the same for Fax. Click Next.

Move Country and CompanyName to the Sorting Order list. (Make sure that Country is above CompanyName.) Click Next.

Click Only Rows Meeting Criteria.

In the Column drop-down list, select [Customers].[Country]; in the Oper. drop-down list, select =; and in Value/Column, type 'Argentina' (include the single quotes). On the next line, select OR from the logical operator drop-down list. In the Column drop-down list, select [Customers].[Country]; in the Oper. drop-down, list select =; and in Value/Column, type 'Brazil' (include the single quotes). Click Next.

The query that will return only South American countries has been filled in for you in Query Statement. Click Parse. (If the statement is not valid, return to step 10.) Click Next.

For the file format, select Delimited.

Set Column Delimiter to Tab. Click Next.

Check Run Immediately and Schedule DTS Package For Later Execution in the When section.

Click the ellipsis button next to Schedule DTS Package For Later Execution.

Click Weekly. Set the Weekly section to Every 1 Week(s) on Mon, Wed, and Fri.

In the Daily Frequency section, select Occurs Once At and set the time to 9:00 a.m. Click OK, and then click Next.

In Name, type South American Customers; in Description, type South American customer list.

For Server Name, select (local). Select Use Windows NT Authentication. Click Next.

Click Finish. The Transferring Data dialog box indicates the progress of the data transfer.

A dialog box indicates when the transfer has completed successfully. Click OK to close the dialog box, and click Done to close the Transferring Data dialog box.

Open Notepad to review the text file (C:\Sqladmin\Exercise\Ch06\Sacust.txt). The file should contain all of the rows in which customer.country equals Argentina or Brazil. You should see all columns except Phone Or Fax.

Exercise: Reviewing the Job Schedule

In this exercise, you will review the job schedule that was created to execute your DTS package.

To verify that the schedule was created

To view the schedule that was created, in the Enterprise Manager console tree expand Management and expand the SQL Server Agent icon, and then click Jobs.

In the details pane, right-click the job name, South American Customers, and then click Properties. Review the properties of the job that was created by the DTS Wizard. Note that the job step command is not viewable because it is encrypted. Click OK to close the job.

DTS Designer

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.

Experienced users can use DTS Designer to integrate, consolidate, and transform data from multiple heterogeneous sources, using complex workflows to simplify the process of building a data warehouse. The next lesson describes data transformations.

Lesson Summary

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.