Lesson 2: Introduction to Data Transformation Services

Data warehouses centralize data to improve corporate decision making. However, the source data is often in a large variety of formats stored 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 using the DTS Import and DTS Export wizards.

After this lesson, you will be able to:

• Describe the purpose of DTS packages
• 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, which allows you to use applications for which an OLE DB provider exists 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, including data warehousing. If you use DTS in this way, you are required to have a normal SQL Server license.

NOTE

---

DTS moves table schema and data 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.0

The DTS Process

The process of transferring data is an integral part of all DBMSs. DTS provides an extensible 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.2 illustrates such a DTS package.

Figure 6.2 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 Chapter 8, "Advanced DTS."

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 variety of data source and destination types such as databases, spreadsheets, text files, and so on.

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 Microsoft Excel.

The DTS Data Pump

The DTS data pump is an OLE DB service provider that allows 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 datastores.

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, and Microsoft JScript, 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 DTS 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.
• 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.

NOTE

---

When copying a table, the DTS wizards, by default, do not copy indexes, triggers, or constraints. If you want to have the package create the table, 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 1: 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

1. In the console tree in SQL Server Enterprise Manager, right-click your server name, point to All Tasks, and then click Import Data. This launches the Data Transformation Services Import wizard.
2. Click Next.

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

3. In Source, select Microsoft OLE DB Provider for SQL Server.
4. In Server, select (local).
5. Select Use Windows NT authentication.
6. In Database, select Northwind. Click Next.
7. In Destination, select Microsoft OLE DB Provider for SQL Server.
8. In Server, select (local).
9. Select Use Windows NT authentication.
10. In Database, select Northwind. Click Next.
11. Select Use a query to specify the data to transfer. Click Next.
12. In Query statement, type

SELECT ProductName, SUM(o.UnitPrice * Quantity) AS Total FROM [Order Details] o 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:\SQLDW\Exercise\Ch06\Query.sql.

13. Click Parse. A confirmation message says, "The SQL statement is valid" if you have typed the statement correctly. Click OK to close the message. Click Next.
14. In the Table(s) list, click the value in the Destination Table column (the default value is Results). Because 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.
15. Click the ellipsis button in the Transform column.
16. Check Drop and re-create destination table. Uncheck Nullable for Total under Mappings. Click OK to close the Column Mappings and Transformations dialog box. Click Next.
17. Ensure that Run immediately is the only option that is checked in the When section.
18. Check Save DTS Package and select SQL Server in the Save section. Click Next.
19. In Name, type Northwind Product Totals. In Description, type Year to date product totals.
20. For Server name, select (local). Select Use Windows NT authentication. Click Next.
21. 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 error is expected because the table does not already exist. The error will not affect the data transfer.
22. 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.
23. 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.
24. Open SQL Server Query Analyzer.
25. To view the imported results in the ProductTotals table, execute the following Transact-SQL statement. The table and product summary data should exist:

SELECT * FROM Northwind..ProductTotals

Exercise 2: 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

1. In the console tree in SQL Server Enterprise Manager, right-click your server name, point to All Tasks, and then click Export Data. This launches the Data Transformation Services Export wizard.
2. Click Next.

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

3. In Source, select Microsoft OLE DB Provider for SQL Server.
4. In Server, select (local).
5. Select Use Windows NT authentication.
6. In Database, select Northwind. Click Next.
7. In Destination, select Text File.
8. In File name, type c:\sqldw\exercise\ch06\sacust.txt. Click Next.
9. Select Use a query to specify the data to transfer. Click Next.
10. Click Query Builder. Click Customers, then click > to add all columns from the Customers table to the Selected columns list.
11. In the Selected columns list, click on Phone and click < to remove it from the list. Do the same for Fax. Click Next.
12. Move Country and CompanyName to the Sorting order list so that Country appears before CompanyName in the list. Click Next.
13. Click Only Rows meeting criteria.
14. 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.
15. 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, repeat steps 10 through 14 to correct it. Click Next.
16. For the file format, select Delimited.
17. Set Column delimiter to Tab. Click Next.
18. Check Run immediately and Schedule DTS Package for later execution in the When section.
19. Click the ellipsis button next to Schedule DTS Package for later execution.
20. Click Weekly. Set the Weekly section to Every 1 week(s) on Mon, Wed, and Fri.
21. In the Daily frequency section, select Occurs once at and set the time to 9:00 A.M. Click OK, then click Next.
22. In Name, type South American Customers; in Description, type South American customer list.
23. For Server name, select (local). Select Use Windows NT authentication. Click Next.
24. Click Finish. The Transferring Data dialog box indicates the progress of the data transfer.
25. 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.
26. Open Notepad to review the text file (C:\SQLDW\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 3: 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

1. In the console tree in SQL Server Enterprise Manager, expand your server, expand Management, and then expand SQL Server Agent.
2. 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. Chapter 8, "Advanced DTS," 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.

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.