MCSA/MCSE Training Kit: Microsoft SQL Server 2000 System Administration by Microsoft Press, Microsoft, Carl Rabeler

From the Publisher

This official MCSE TRAINING KIT teaches database administrators how to set up and support SQL Server 2000-as they prepare for MCP Exam 70-228, an elective on the MCSE track for Microsoft Windows 2000 and a core exam for MCDBA certification. Topics map directly to the objectives measured by the MCP exam, including installing and configuring SQL Server 2000, creating databases, optimizing database performance, extracting and transforming data, managing security, and monitoring and troubleshooting SQL Server system activity. Students learn through an integrated system of skill-building tutorials, case study examples, and self-assessment exercises. An economical alternative to classroom instruction, this kit enables students to set their own pace and learn by doing!

Booknews

Designed for the Microsoft Certified Professional (MCP) exam, this instruction book presents lessons and exercises on installing, configuring, and troubleshooting SQL Server 2000. Attention is given to topics like database options, Data Transformation Services, replication services, security, back-ups, and restorations. All of the book's content, sample databases, and technical white papers are included on a CD-ROM. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Product Details

• Table of Contents
• Read an Excerpt
• Read a Sample Chapter

Table of Contents

Read an Excerpt

Chapter 7: Populating a Database

About This Chapter

Before You Begin

• A computer that meets or exceeds the minimum hardware requirements listed in Table 2.1 in the Lesson 1 section of Chapter 2.
• Microsoft Windows 2000 Server running on your computer on an NTFS partition.
• A computer with a NetBIOS name of SelfPacedCPU, configured as a domain controller in the SelfPacedSQL.MSFT domain.
• Installed a default instance and at least one named instance of SQL Server 2000 (see Chapter 2).

Lesson 1: Transferring and Transforming Data

• Describe the process of evaluating the quality and consistency of data in an external data source prior to data import
• Understand the types of data transformations that may be necessary when importing data from existing data sources
• Describe the tools provided by SQL Server 2000 for importing data

Estimated lesson time: 15 minutes

Importing Data

• Determine the consistency of the existing data within the external data source. The external data source may not have enforced consistency when data was initially input (for example, if the names of states were entered as two-letter abbreviations in some instances and were fully spelled out in other instances).
• Determine whether additional columns must be added. The existing data may be internally consistent but not include necessary columns because these values were assumed (such as values for country or an international telephone area code).
• Determine whether the existing data format should be modified. The existing data may be internally consistent but not be represented in the manner you want to use in the destination database (for example, requiring a change in the date format or the conversion of numerical values to more readable string values, such as 1, 2, and 3 being converted to poor, average, and excellent).
• Determine whether existing data columns should be modified. The existing data may be internally consistent but contain columns that need to be aggregated or separated (such as separating first and last names into separate columns or summarizing daily data into weekly or monthly data).
• Determine whether the import of data will be a one-time task or a task that must be performed periodically. The existing data may need to be migrated from an existing legacy system that will be retired or may need to be extracted from an existing system from which you will extract data on a weekly or monthly basis.
• Determine how to access the existing data. Is the external data source directly accessible, and do you have permission to access the data directly? (If not, the data will have to be exported into a format that SQL Server 2000 can work with, such as a delimited text file.)

DTS Data Transformations

The type and extent of modifications that must be made will help determine the SQL Server 2000 data transfer tool that you will use, and when you will perform the modifications. Also, whether the data import is a one-time task or a periodic task will frequently determine the tool you will use and how much you automate the necessary transformations. Planning and testing (using data subsets) is essential, particularly for large data sets.

Introducing the Data Transfer Tools

Table 7.1  Data Transfer Tools and Their Functions

Tool	Description
DTS	DTS is a graphical tool used to import, export, and transform data. DTS can work directly with a wide variety of data sources. DTS creates packages that can be scheduled. DTS can also import and export database objects schema (meta data) between SQL Server instances.
Bcp	Bcp is a command-prompt utility used to copy data from a text file to a SQL Server 2000 table or view (or from a SQL Server 2000 table or view to a text file) using ODBC. The transformation capabilities of Bcp are limited and require cryptic format files. Working with Microsoft or third-party databases is a two-step process.
BULK INSERT Transact-SQL statement	BULK INSERT is a Transact-SQL command used to copy data from an ASCII text file to a SQL Server 2000 table or view (but not from SQL Server 2000 to a text file) using OLE DB. The BULK INSERT statement provides the same functionality of Bcp (and has the same limitations) in a Transact-SQL statement and can be embedded in a DTS package.

Lesson Summary

Lesson 2: Introducing Microsoft Data Transformation Services (DTS)

• Describe the components of a DTS package
• Describe the data sources to which DTS can establish direct connections
• Describe the type of data transformations that are available
• Describe the DTS workflow options
• Describe the DTS package storage options
• Describe each of the DTS tools

Estimated lesson time: 15 minutes

Understanding a DTS Package

DTS Connections

A DTS package must have a valid data source and a valid data destination to which it connects. It can connect to additional data sources during execution (such as lookup tables located on a third data connection). Table 7.2 describes the variety of connections that you can establish during the package creation process.

Table 7.2  Connection Types

Connection Type	Description
Data source connection	A connection to a standard database (such as Microsoft SQL Server 2000, Microsoft Access 2000, Oracle, dBase, or Paradox), an OLE DB connection to an ODBC data source, a Microsoft Excel 2000 spreadsheet, an HTML source, or any other OLE DB provider. The properties of the data source connection specify the necessary connection parameters.
File connection	A connection to a text file (normally delimited). The properties of the file connection specify the format of the text file. There are multiple formats you can use. For example, the text file format can be either a delimited or a fixed field format.
Data link connection	A connection to an intermediate file (.UDL file) that stores a connection string to create an OLE DB connection that is resolved at run time. The data link connection allows you to encapsulate the connection properties into a separate .UDL data link file. You can then edit the connection string in the data link file (from one data source to another) without changing the SQL Server 2000 DTS package.

DTS Tasks

A DTS package can perform a plethora of tasks, either sequentially or in parallel. Parallel tasks run independently of each other (using separate operating system threads to enhance performance), whereas sequential tasks execute in a defined order based on the success, failure, or completion of predecessor tasks. A DTS task is a discrete unit of work that is part of a data movement and transformation process (such as copying the contents of a text file to a SQL Server 2000 table). DTS tasks that transform data (such as the Parallel Data Pump, the Transform Data, and the Data Driven Query tasks) are based on an architectural component called the DTS data pump. The DTS data pump is an OLE DB service provider that provides the interfaces and the means to import, export, and transform data from an OLE DB data source to an OLE DB destination.

SQL Server 2000 provides a number of DTS tasks that transform data, copy and manage data and meta data, and function as jobs. These tasks are accessed through the DTS Designer tool (except the Parallel Data Pump task, which can only be accessed programmatically). Table 7.3 describes the tasks that are available through DTS Designer (all of which are also accessible programmatically).

DTS can perform column-level transformations on data during the importation process. These transformations can be as simple as changing column names and as complex as your transformation process requires. Using DTS Designer, you can use the Transform Data task or the Data Driven Query task, or you can write an ActiveX script. Using Visual Basic or Visual C++, you can also write transformations programmatically and use the Parallel Data Pump task. You perform a transformation by mapping a relationship between one or more columns in the data source with one or more columns in the data destination. You then define the transformations you want to occur during importation....

Read a Sample Chapter

Populating a Database

• About This Chapter
• Before You Begin
• Lesson 1: Transferring and Transforming Data
• Importing Data
• DTS Data Transformations
• Introducing the Data Transfer Tools
• Lesson Summary
• Lesson 2: Introducing Microsoft Data Transformation Services (DTS)
• Understanding a DTS Package
• DTS Package Storage
• Introducing DTS Tools
• Lesson Summary
• Lesson 3: Transferring and Transforming Data with DTS Graphical Tools
• Using the DTS Import/Export Wizard
• Practice: Transferring Tables and Data from the Northwind Database Using the DTS Import/Export Wizard
• Using DTS Designer
• Practice: Creating a Data Transfer and Transform Package Using DTS Designer
• Extending DTS Package Functionality
• Lesson Summary
• Lesson 4: Working with DTS Packages
• Understanding DTS Package Storage Options
• Using DTS Package Execution Utilities
• Using DTS Package Logs and Exception Files
• Performing Disconnected Edits
• Lesson Summary
• Lesson 5: Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
• Copying Data in Bulk Using Text Files
• Using Bcp
• Using the BULK INSERT Transact-SQL Statement
• Optimizing Bulk Copy Operations
• Lesson Summary
• Review

About This Chapter

This chapter prepares you to populate your database from an existing data source. The chapter begins with a discussion of the data transformation, consistency, and validation issues you will encounter when you import data from one or multiple sources into a SQL Server 2000 database. Next, you are introduced to the primary SQL Server 2000 tools used for populating a database. Finally, you learn how and when to use each of these tools.

Before You Begin

To complete this chapter, you must have

• A computer that meets or exceeds the minimum hardware requirements listed in Table 2.1 in the Lesson 1 section of Chapter 2.
• Microsoft Windows 2000 Server running on your computer on an NTFS partition.
• A computer with a NetBIOS name of SelfPacedCPU, configured as a domain controller in the SelfPacedSQL.MSFT domain.
• Installed a default instance and at least one named instance of SQL Server 2000 (see Chapter 2).

Lesson 1: Transferring and Transforming Data

After you have created your user database, you need to populate it with data.
Frequently, this consists of importing and transforming existing data from one or more external data sources (such as another database system) to SQL Server 2000. In this lesson, you learn the issues you will face when you choose to import data from another data source. This lesson will also introduce you to the primary tools used to import and transform data, including a brief discussion of the capabilities of each tool. These tools include DTS, Bcp, and the BULK INSERT Transact-SQL statement.

• Describe the process of evaluating the quality and consistency of data in an external data source prior to data import
• Understand the types of data transformations that may be necessary when importing data from existing data sources
• Describe the tools provided by SQL Server 2000 for importing data

Estimated lesson time: 15 minutes

Importing Data

Populating your SQL Server 2000 user database frequently consists of the process of importing existing data from external data sources to a newly created destination database. These external data sources include Microsoft or third-party databases, spreadsheets, or text files. Before you can import this data, you must perform the following preliminary tasks to evaluate the external data and determine the steps that will be involved in the import process. These preliminary steps will also help you select the appropriate tool to use.

• Determine the consistency of the existing data within the external data source. The external data source may not have enforced consistency when data was initially input (for example, if the names of states were entered as two-letter abbreviations in some instances and were fully spelled out in other instances).
• Determine whether additional columns must be added. The existing data may be internally consistent but not include necessary columns because these values were assumed (such as values for country or an international telephone area code).
• Determine whether the existing data format should be modified. The existing data may be internally consistent but not be represented in the manner you want to use in the destination database (for example, requiring a change in the date format or the conversion of numerical values to more readable string values, such as 1, 2, and 3 being converted to poor, average, and excellent).
• Determine whether existing data columns should be modified. The existing data may be internally consistent but contain columns that need to be aggregated or separated (such as separating first and last names into separate columns or summarizing daily data into weekly or monthly data).
• Determine whether the import of data will be a one-time task or a task that must be performed periodically. The existing data may need to be migrated from an existing legacy system that will be retired or may need to be extracted from an existing system from which you will extract data on a weekly or monthly basis.
• Determine how to access the existing data. Is the external data source directly accessible, and do you have permission to access the data directly? (If not, the data will have to be exported into a format that SQL Server 2000 can work with, such as a delimited text file.)

DTS Data Transformations

After you have evaluated the data in each external data source, you need to determine how to proceed. Occasionally, changes to data can be made in the external data source, but usually these changes cannot be made in the external data source without either breaking existing applications (for example, adding columns or changing data formats) or consuming too much time (for example, manually enforcing data consistency where none existed previously). These changes can either be made after the data has been imported into SQL Server, using temporary tables and using Transact-SQL statements to scrub and cleanse the data, or can be made during the import process itself. Changes to the data made during the import and export process are referred to as DTS transformations. A DTS transformation occurs when one or more operations or functions are applied against data before that data arrives at the destination. The data at the source is not changed. Transformations make it easy to implement data scrubbing, conversions, and complex data validations during the import and export process.

The type and extent of modifications that must be made will help determine the SQL Server 2000 data transfer tool that you will use, and when you will perform the modifications. Also, whether the data import is a one-time task or a periodic task will frequently determine the tool you will use and how much you automate the necessary transformations. Planning and testing (using data subsets) is essential, particularly for large data sets.

Introducing the Data Transfer Tools

SQL Server 2000 provides a number of tools for importing and exporting data. These tools have differing capabilities to extract subsets of data from an existing data source and to transform data. Table 7.1 briefly describes each of these primary data transfer tools and their capabilities.

Table 7.1  Data Transfer Tools and Their Functions

Tool	Description
DTS	DTS is a graphical tool used to import, export, and transform data. DTS can work directly with a wide variety of data sources. DTS creates packages that can be scheduled. DTS can also import and export database objects schema (meta data) between SQL Server instances.
Bcp	Bcp is a command-prompt utility used to copy data from a text file to a SQL Server 2000 table or view (or from a SQL Server 2000 table or view to a text file) using ODBC. The transformation capabilities of Bcp are limited and require cryptic format files. Working with Microsoft or third-party databases is a two-step process.
BULK INSERT Transact-SQL statement	BULK INSERT is a Transact-SQL command used to copy data from an ASCII text file to a SQL Server 2000 table or view (but not from SQL Server 2000 to a text file) using OLE DB. The BULK INSERT statement provides the same functionality of Bcp (and has the same limitations) in a Transact-SQL statement and can be embedded in a DTS package.

When you are populating a database from existing data stored in an external data source, you must evaluate that data to determine what transformations, if any, must be performed on that data prior to or during importation. You must determine whether the import is a one-time task or will be a periodic task. You must also determine how you will access this data, either directly or through an exported text file. These factors will determine the data transfer tool you use, and when you transform the data.

Lesson 2: Introducing Microsoft Data Transformation Services (DTS)

DTS is a powerful set of graphical tools (and programmable objects) that you can use to import, export, and transform data to and from a wide variety of data sources and destinations. In this lesson, you learn about the components of DTS packages, including connections, tasks, transformations, and workflow. You are also introduced to storage options for DTS packages. Finally, this lesson introduces the DTS tools, including the DTS Import/Export Wizard (Dtswiz.exe), DTS Designer, and the DTS package execution utilities. Subsequent lessons will teach you how to use each of these tools.

• Describe the components of a DTS package
• Describe the data sources to which DTS can establish direct connections
• Describe the type of data transformations that are available
• Describe the DTS workflow options
• Describe the DTS package storage options
• Describe each of the DTS tools

Estimated lesson time: 15 minutes

Understanding a DTS Package

You can create a DTS package to connect to a data source, copy and manage data and database objects, run tasks as jobs from within a package, transform the data, and then store the transformed data and the database objects to the same data source or to a different data destination. You can also notify a user (or process) of the success or failure of the package, including attaching a file to an e-mail message. You define these tasks as discrete steps (known as DTS tasks) and control the execution using precedence constraints (such as performing a certain task if a task succeeds and a different task if the task fails). You create a DTS package in the DTS Import/Export Wizard, in DTS Designer, or programmatically.

DTS Connections

A DTS package must have a valid data source and a valid data destination to which it connects. It can connect to additional data sources during execution (such as lookup tables located on a third data connection). Table 7.2 describes the variety of connections that you can establish during the package creation process.

Table 7.2  Connection Types

Connection Type	Description
Data source connection	A connection to a standard database (such as Microsoft SQL Server 2000, Microsoft Access 2000, Oracle, dBase, or Paradox), an OLE DB connection to an ODBC data source, a Microsoft Excel 2000 spreadsheet, an HTML source, or any other OLE DB provider. The properties of the data source connection specify the necessary connection parameters.
File connection	A connection to a text file (normally delimited). The properties of the file connection specify the format of the text file. There are multiple formats you can use. For example, the text file format can be either a delimited or a fixed field format.
Data link connection	A connection to an intermediate file (.UDL file) that stores a connection string to create an OLE DB connection that is resolved at run time. The data link connection allows you to encapsulate the connection properties into a separate .UDL data link file. You can then edit the connection string in the data link file (from one data source to another) without changing the SQL Server 2000 DTS package.

DTS Tasks

A DTS package can perform a plethora of tasks, either sequentially or in parallel. Parallel tasks run independently of each other (using separate operating system threads to enhance performance), whereas sequential tasks execute in a defined order based on the success, failure, or completion of predecessor tasks. A DTS task is a discrete unit of work that is part of a data movement and transformation process (such as copying the contents of a text file to a SQL Server 2000 table). DTS tasks that transform data (such as the Parallel Data Pump, the Transform Data, and the Data Driven Query tasks) are based on an architectural component called the DTS data pump. The DTS data pump is an OLE DB service provider that provides the interfaces and the means to import, export, and transform data from an OLE DB data source to an OLE DB destination.

SQL Server 2000 provides a number of DTS tasks that transform data, copy and manage data and meta data, and function as jobs. These tasks are accessed through the DTS Designer tool (except the Parallel Data Pump task, which can only be accessed programmatically). Table 7.3 describes the tasks that are available through DTS Designer (all of which are also accessible programmatically).

DTS can perform column-level transformations on data during the importation process. These transformations can be as simple as changing column names and as complex as your transformation process requires. Using DTS Designer, you can use the Transform Data task or the Data Driven Query task, or you can write an ActiveX script. Using Visual Basic or Visual C++, you can also write transformations programmatically and use the Parallel Data Pump task. You perform a transformation by mapping a relationship between one or more columns in the data source with one or more columns in the data destination. You then define the transformations you want to occur during importation.

Table 7.3  Tasks Available through DTS Designer

Category	Task	Description
Tasks that copy and manage data and meta data	Bulk Insert task	Runs the BULK INSERT Transact-SQL statement from within a DTS package. This task provides the fastest way to copy information into a table or view, but it does not log error-causing rows. If you need to capture error-causing rows to an exception file, use the Transform Data task instead.
	Execute SQL task	Runs Transact-SQL statements during package execution. You can perform a number of operations with Execute SQL task, which include dropping a table and running stored procedures.
	Copy SQL Server Objects task	Copies SQL Server database objects (meta data) from one SQL Server instance to another. This task can transfer objects from one instance of SQL Server 7.0 to another; from an instance of SQL Server 7.0 to SQL Server 2000; or from one instance of SQL Server 2000 to another instance of SQL Server 2000.
	Transfer Database Objects tasks	A collection of tasks that copy server-wide information (the Copy SQL Server Objects task copies only database-specific information) from one SQL Server instance to another. These tasks include the Transfer Database task, the Transfer Error Messages task, the Transfer Logins task, the Transfer Jobs task, and the Transfer Master Stored Procedures task. These tasks are used by the Copy Database Wizard.
Tasks that transform data	Transform Data task	Copies, transforms, and inserts data from a data source to a data destination. This task is the most basic implementation of the data pump engine in DTS.
	Data Driven Query task	Selects, customizes, and executes one of several Transact-SQL operations (such as an update or a delete) on a row based on the data in the row. Use this task if the Transform Data task and the Bulk Insert task do not meet the requirements of your application.
Tasks that function as jobs	ActiveX Script task	Runs an ActiveX script. You can use this task to write code to perform functions that are not available in DTS Designer.
	Dynamic Properties task	Retrieves data from an outside source and assigns values retrieved to selected package properties. External sources can be an .INI file, data file, query, global variable, environmental variable, or a constant.
	Execute Package task	Runs other DTS packages as part of a workflow. Do not use this task recursively because it could generate a stack overflow, which could result in MMC shutting down.
	Execute Process task	Runs an executable program or batch file. This task can be used to open any standard application, such as Microsoft Excel, but it is used primarily to run batch files or business applications that work against a data source.
	File Transfer Protocol task	Downloads data from a remote server or an Internet location using FTP. The FTP task and Ftp.exe use the same connection method.
	Send Mail task	Sends an e-mail message as a task. For example, notification can be sent to an administrator about the success or failure of a backup operation. In order to use this task, you need to install a MAPI client on the instance of SQL Server you are running.

Table 7.4 describes the types of transformations that are available.

Table 7.4  Available Types of Transformations

Transformation Type	Description
Copy Column	Copies data from a single column to a single column (or multiple column to multiple column). By default, allows all possible data type conversions and automatically truncates text (when necessary) without error notification.
ActiveX Script	Uses an ActiveX script to transform (and truncate) data between one or more source and destination columns on a row-by-row basis.
Date Time String	Converts a date or time value in a source column to a different format in the destination column. Both columns must be a string data type.
Lowercase String	Converts string data to lowercase characters and to the destination data type (if necessary) from a source column to the destination column, truncating data as necessary. Both columns must be a string data type.
Uppercase String	Converts string data to uppercase characters and to the destination data type (if necessary) from a source column to the destination column, truncating data as necessary. Both columns must be a string data type.
Middle of String	Extracts a substring of string data from a source column and copies it to a destination column, truncating data if necessary. Can also perform case conversion. Both columns must be a string data type.
Trim String	Removes leading, trailing, and embedded white space from string data in a source column and copies it to a destination column, truncating data if necessary. Can also perform case conversion. Both columns must be a string data type.
Read File	Opens and copies the contents of a file specified in the source column to a destination column. The source column must be a string data type. The destination column must be a string or binary data type.
Write File	Copies the contents of a source column to a file specified string or binary data type.

DTS Package Workflow

DTS uses steps and precedence constraints to order tasks within a DTS package. Steps define the sequence in which tasks within a package execute. In DTS Designer (or programmatically), you use precedence constraints to control this sequence. Precedence constraints sequentially link each task within a package. A task can have multiple precedence constraints. Tasks without precedence constraints operate in parallel. There are three types of precedence constraints, as shown in Table 7.5.

Table 7.5   Precedence Constraints and Their Functions

Precedence Constraint	Description
Unconditional	If Task 2 is linked to Task 1 by an Unconditional constraint, Task 2 will wait until Task 1 completes and then will execute, regardless of the success or failure of Task 1.
On Success	If Task 3 is linked to Task 2 by an On Success constraint, Task 3 will wait until Task 2 completes, and then will only execute if Task 2 completed successfully.
On Failure	If Task 4 is linked to Task 2 by an On Failure constraint, Task 4 will wait until Task 2 completes and then will only execute if Task 2 failed to complete successfully.

For example, assume Task 1 is a Drop Table task, Task 2 is a Create Table task, Task 3 is a Populate Table task, and Task 4 is a Restore Table task. If the table does not exist, Task 1 will fail and Task 2 will create the table. If the table does exist, Task 1 will drop the table and Task 2 will create the table. Next, if Task 2 creates the table successfully, Task 3 will populate the table. However, if Task 2 fails to create the table successfully, Task 4 will restore the table.

DTS Package Storage

You can store a DTS package to SQL Server 2000, SQL Server 2000 Meta Data Services, a Microsoft Visual Basic file, or a structured storage file. When you save a DTS package, all DTS connections, tasks, transformations, and workflow steps are saved. Table 7.6 describes each of these storage options.

Introducing DTS Tools

You create a DTS package using the DTS Import/Export Wizard, DTS Designer, or programmatically. The DTS Import/Export Wizard is the simplest way to create DTS packages to copy data between data sources, but it limits the complexity of the transformations, the addition of multiple DTS tasks, and the complexity of DTS task workflows. The DTS Import/Export Wizard is available through SQL Server Enterprise Manager and from the Start menu in the Microsoft SQL Server program group. DTS packages created using the DTS Import/Export Wizard can be further customized using DTS Designer, Visual Basic, or Visual C++.

Table 7.6  DTS Storage Options

Storage Location	Description
SQL Server 2000	Stored as a table in the msdb database on any instance of SQL Server 2000. This is the default save option. Multiple packages and multiple versions can be stored. When you save a package to SQL Server 2000, you have the option of securing the packages with one or more passwords.
Meta Data Services	Stored in the repository database in Meta Data Services on your computer. Allows tracking of columns and tables used in the source and destination, including the lineage (ancestry or original source) of data in a particular row. You can secure a package saved to Meta Data Services by using its own security.
Visual Basic file	Stored in Visual Basic code that you can later open and modify using Visual Basic or Visual C++. You can secure packages saved to a Visual Basic file using a system such as Microsoft Visual SourceSafe.
Structured storage file	Stored in an operating system file. Allows storage and transfer of a DTS package independent of any SQL Server database. Multiple packages and multiple versions can be stored in a single file. When you save a package to a structured storage file, you have the option of securing the packages with one or more passwords.

DTS Designer allows you to modify existing DTS packages or create new DTS packages using graphical objects to help build DTS packages containing complex workflows (such as multiple connections and event-driven logic). DTS Designer is available through the Data Transformation Services container in the SQL Server Enterprise Manager console tree.

You can also create DTS packages using Visual Basic and Visual C++. This method is useful for developers who need to access the DTS object model directly and exert a fine degree of control over package operations. Packages created programmatically can be further customized using DTS Designer. Model templates designed for specific solutions are available for customization (such as data driven queries).

DTS also includes package execution utilities to run and schedule DTS packages from a command prompt. These utilities include the DTS Run (Dtsrunui.exe) utility and the Dtsrun command. The DTS Run utility is an interactive command prompt utility that uses a dialog box to execute a DTS package (and create a Dtsrun batch file), whereas Dtsrun can execute a DTS package from a command prompt using command switches (frequently stored in a batch file).

Lesson Summary

DTS uses packages to connect to and move data between a wide variety of OLE DB data sources. A DTS package can extract data from one or more of these data sources, perform simple or complex transformations on this data, and then store the transformed data to one or more of these data destinations. You can use workflow logic (precedence constraints) within the DTS package. The DTS package itself can be stored in SQL Server 2000, in SQL Server Meta Data Services, as a Visual Basic file, or as a structured storage file. You can create a DTS package using the DTS Import/Export Wizard, DTS Designer, Visual Basic, or Visual C++.

Lesson 3: Transferring and Transforming Data with DTS Graphical Tools

DTS provides two graphical tools that you can use to create DTS packages that transfer and transform data. In this lesson, you will learn to use each of these. First, you will learn to use the DTS Import/Export Wizard to create simple transformations. Then, you will learn to use DTS Designer to create more complex transformations and workflows. You will also learn to save these packages in a variety of formats. Finally, you will learn about extending the functionality of DTS packages.

• Use the DTS Import/Export Wizard to create a DTS package
• Use DTS Designer to create a DTS package
• Save DTS packages to a variety of formats
• Describe additional functionality that can be added to DTS packages

Estimated lesson time: 60 minutes

Using the DTS Import/Export Wizard

The DTS Import/Export Wizard can be started from the Microsoft SQL Server program group on the Start menu and from within SQL Server Enterprise Manager. Within SQL Server Enterprise Manager, you can start this wizard by clicking the Tools menu and then pointing to Wizards, or by right-clicking the Data Transformation Services container in the console tree, pointing to All Tasks, and then clicking either Import Data or Export Data (both bring up the same wizard). The DTS Import/Export Wizard guides you through the steps to import or export data between many different formats.

The first step in this process is selecting the data source in the Choose A Data Source page. The default data source is the Microsoft OLE DB Provider for SQL Server. This data source is used to connect to an instance of SQL Server. Select the data-specific driver for the data storage format from which you want to copy data (such as a text file or an Oracle database) from the Data Source drop-down list. The remaining properties you will define on this page depend upon the data source selected. For example, if your data source is SQL Server, you provide the server name, authentication type, and database. See Figure 7.1.

Figure 7.1  Selecting the data source in the DTS Import/Export Wizard. (Image unavailable)

If you are using a different data source, other connection information is required. For example, if you are copying data from a text file, you must provide the filename on the Choose A Data Source page, followed by file format information (including fixed or delimited fields, file type, row and column delimiters, and text qualifiers), which you select on the the Select File Format page and the Specify Column Delimiter page. See Figures 7.2, 7.3, and 7.4.

Figure 7.2  Specifying a text file as the data source. (Image unavailable)

Figure 7.3  Selecting the file format, field type, and text qualifier. (Image unavailable)

Figure 7.4  Specifying the column delimiter. (Image unavailable)

The next step in this process is selecting the data destination in the Choose A Destination page. Again, the default data source is the Microsoft OLE DB Provider for SQL Server, for which you must provide a server name and connection information. You can select from a wide variety of data destinations. Both your data source and your data destination can be products other than SQL Server 2000. See Figure 7.5. For example, you could use DTS to copy data from Oracle to dBase. With SQL Server 2000, you can create a new database on the fly. If you create a new database, the physical location will be the same disk and folder as the master database. The only database properties you can choose in the Create Database dialog box are the name of the database and the sizes of the data file and the transaction log file. See Figure 7.6.

Figure 7.5  Selecting a destination for your data. (Image unavailable)

Figure 7.6  Specifying the name and properties of the new database. (Image unavailable)

After selecting your data source and your data destination, you specify or filter the data you will copy in the Specify Table Copy Or Query page. (This page will not appear if you are creating a new database.) Your choices will vary depending upon the data source and the data destination. If the data source is a database, you can perform a simple copy of data (unfiltered and unordered) by selecting the Copy Table(s) And View(s) From The Source Database option button, or you can perform a more complex copy requiring a Transact-SQL query (selecting only rows matching specified selection criteria and in a certain order) by selecting the Use A Query To Specify The Data To Transfer option button. In addition, if both the data source and destination are SQL Server 7.0 or SQL Server 2000 databases, you can copy database objects (such as stored procedures and logins) by selecting the Copy Objects And Data Between SQL Server Databases option button. See Figure 7.7.

Figure 7.7  Specifying the type of copy operation. (Image unavailable)

Copying Entire Tables and Views

If you choose to copy entire tables or views, you then select all or some of the tables or views for copying in the Select Source Tables And Views page. By default, the destination name for each table or view will be the name of the table or view being copied. You can create new table or view names, or select different existing tables or views. See Figure 7.8.

Figure 7.8  Selecting source tables and views. (Image unavailable)

If you perform no other action, the contents of each selected table or view will be copied without changes. If the destination table exists, by default, the data being copied will be appended to the existing data. If the destination table does not exist, the table will be created using the specified name. If you want to change these defaults, on the Select Source Tables And Views page, click the ellipsis in the Transform column for each table or view you want to transform. On the Column Mappings tab of the Column Mappings And Transformations dialog box, you can specify the mappings between source and destination columns, create a new destination table, edit the CREATE TABLE Transact-SQL statement (if a new table or view is being created), choose to delete or append rows in the destination table (if an existing table is detected), enable insert identity (if an identity column is detected), and change the data type (if a valid data conversion is available). See Figure 7.9.

Figure 7.9  Changing the column mappings and transformations. (Image unavailable)

You can also specify unique transformations using either VBScript or JScript (VBScript is the default) on the Transformations tab of the Column Mappings And Transformations dialog box. To transform data while it is being copied, edit the script in the test area to customize columns before copying them to the destination. See Figure 7.10. Although you can perform some customized transformations using the DTS Import/Export Wizard, DTS Designer is more appropriate for complex scripting through the use of ActiveX scripting.

Querying to Specify the Data

If you choose to use a query to specify the data to transfer, you can write your own Transact-SQL script in the space provided in the Type SQL Statement page, browse and import an existing script, or click the Query Builder button to graphically create a script using DTS Query Designer. DTS Query Designer is a graphical query-building tool. A Parse button is provided to test the syntax of your script. See Figure 7.11.

Figure 7.10  Specifying transformation options. (Image unavailable)

Figure 7.11  Creating a script using the Query Designer. (Image unavailable)

After you enter your Transact-SQL script and click Next, you can select and then preview your results by clicking the Preview button in the Select Source Tables And Views page (see Figure 7.8) to verify whether the query produces the results you intend. If you perform no other action, the results of the query are copied to a new table called Results. You can modify this name. You can also click the ellipsis in the Transform column in the Select Source Tables and Views page (see Figure 7.8) to modify the column mappings and specify custom transformations in a manner similar to that discussed earlier. See Figure 7.12.

Figure 7.12  Modifying the column mappings and transformations for your query. (Image unavailable)

Copying Objects and Data Between SQL Server Databases

If you choose to copy objects and data between SQL Server databases, you can specify which objects you want to transfer between SQL Server instances in the Select Objects To Copy page. You can only copy between SQL Server 7.0 instances or SQL Server 2000 instances or from a SQL Server 7.0 instance to a SQL Server 2000 instance (not from SQL Server 2000 to SQL Server 7.0). By default, destination objects are created for all objects being copied (all objects are copied by default), all corresponding destination objects are dropped before the new ones are created, and all dependent objects are included in the transfer of data. In addition, by default, all data is copied and all existing data on the destination is deleted. See Figure 7.13. You can limit the objects being copied (such as only certain tables or stored procedures, or no indexes).

Saving and Scheduling Packages

The final step in the DTS Import/Export Wizard for any of the types of transformations described so far is to choose to run the package immediately, or to choose to save or to schedule the package on the Save, Schedule, And Replicate Package page. See Figure 7.14. By default, the package will run immediately and will not be saved or scheduled. You can choose to schedule it to run at a later time as a job under the auspices of SQL Server Agent. Jobs and scheduling are covered in Chapter 13. You can also choose to save the package in any of the supported formats. Choosing between these formats is covered in Lesson 4.

Figure 7.13  Selecting objects to copy. (Image unavailable)

Figure 7.14  Choosing to save or schedule the package. (Image unavailable)

Practice: Transferring Tables and Data from the Northwind Database Using the DTS Import/Export Wizard

In this practice you transfer tables and data from the Northwind database to a new database using the DTS Import/Export Wizard. Then, you modify these tables and summarize data in the NorthwindReportData database.

To transfer tables and data from the Northwind Database using the DTS Import/Export Wizard

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain server as Administrator.
2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Import And Export Data.

The DTS Import/Export Wizard appears.

3. Click Next.

The Choose A Data Source page appears.

4. In the Data Source drop-down list, verify that the selected data source is Microsoft OLE DB Provider for SQL Server.
5. In the Server drop-down list, select SelfPacedCPU\MyNamedInstance.
6. Verify that the Use Windows Authentication option button is selected.
7. In the Database drop-down list, select Northwind and then click Next.

The Choose A Destination page appears.

8. In the Destination drop-down list, verify that the selected data destination is Microsoft OLE DB Provider for SQL Server.
9. In the Server drop-down list, select SelfPacedCPU.
10. Verify that the Use Windows Authentication option button is selected.
11. In the Database drop-down list, select <New>.

The Create Database dialog box appears.

12. In the Name text box, type NorthwindReportData and then click OK.

The Choose A Destination page reappears displaying the new database.

13. Click Next.

The Specify Table Copy Or Query page appears.

14. Verify that the Copy Table(s) And View(s) From The Source Database option button is selected and then click Next.

The Select Source Tables And Views page appears.

15. Click the Select All button.

Notice that the name for each destination table is automatically filled in using the same name as the source table.

16. Click Next.

The Save, Schedule, And Replicate Package page appears.

17. Verify that the Run Immediately check box is selected.
18. Select the Save DTS Package check box, verify that the SQL Server option button is selected, and then click Next.

The Save DTS Package page appears.

19. In the Name text box, type NorthwindTableCopy and then click Next.

The Completing The DTS Import/Export Wizard page appears.

20. Click the Finish button.

The Executing Package dialog box appears displaying the status of the package execution, showing each step. When the package finishes executing, a DTS Import/Export Wizard message box appears stating that 29 tables were successfully copied from Microsoft SQL Server to Microsoft SQL Server.

21. Click OK.
22. In the Executing Package page, briefly review the successfully completed steps and then click the Done button.
23. In the SQL Server Enterprise Manager console tree, expand the default instance and then expand Databases.

Notice that the NorthwindReportData database appears (you might need to press the F5 key to refresh the console tree).

To modify tables and summarize data in the NorthwindReportData database using the DTS Import/Export Wizard

1. Right-click NorthwindReportData, point to All Tasks, and then click Import Data.

The DTS Import/Export Wizard appears.

2. Click Next.

The Choose A Data Source page appears.

3. In the Data Source drop-down list, verify that the selected data source is Microsoft OLE DB Provider for SQL Server.
4. In the Server drop-down list, select SelfPacedCPU.
5. Verify that the Use Windows Authentication option button is selected.
6. In the Database drop-down list, select Northwind and then click Next.

The Choose A Destination page appears.

7. In the Destination drop-down list, verify that the selected data destination is Microsoft OLE DB Provider for SQL Server.
8. In the Server drop-down list, verify that SelfPacedCPU is selected.
9. Verify that the Use Windows Authentication option button is selected.
10. In the Database drop-down list, verify that NorthwindReportData is selected and then click Next.

The Specify Table Copy Or Query page appears.

11. Select the Use A Query To Specify The Data To Transfer option button and then click Next.

The Type SQL Statement page appears.

12. Click the Browse button.

The Open dialog box appears.

13. Using the Look In drop-down list, browse to C:\SelfPacedSQL\CH_7 and open the Query.sql script.

The contents of the Query.sql script appear in the Query Statement box.

14. Click Next.

The Select Source Tables And Views page appears.

15. Click the Results cell in the Destination column and type TotalValue.

Make sure you type TotalValue as a single word with no spaces.

16. Click the Preview button.

The View Data dialog box appears displaying the results of the query.

17. Click OK.

The Select Source Tables And Views page reappears.

18. Click the ellipsis in the Transform column.

The Column Mappings And Transformations dialog box appears.

19. Click the Edit SQL button.

The Create Table SQL Statement dialog box appears.

20. Review the Transact-SQL statement.

Notice that the TotalValue column allows nulls.

21. Click OK to close the Create Table SQL Statement dialog box.

The Column Mappings And Transformations dialog box reappears.

22. In the Mappings grid, clear the Nullable check box for the TotalValue row.
23. Click the Edit SQL button to review the Transact-SQL statement.

The Create Table SQL Statement dialog box appears. Notice that the TotalValue column no longer allows nulls.

24. Click OK to close the Create Table SQL Statement dialog box.
25. Click OK to close the Column Mappings And Transformations dialog box.

The Select Source Tables And Views page reappears.

26. Click Next.

The Save, Schedule, And Replicate Package page appears.

27. Verify that the Run Immediately button is selected.
28. Select the Save DTS Package check box.
29. Select the SQL Server Meta Data Services option button, and then click Next.

The Save DTS Package page appears.

30. In the Name text box, type NorthwindTableTransform and then click Next.

The Completing The DTS Import/Export Wizard page appears.

31. Click the Finish button.

The Executing Package dialog box appears displaying the status of the package execution, showing each step. When the package finishes executing, a DTS Import/Export Wizard message box appears stating that one table was successfully copied from Microsoft SQL Server to Microsoft SQL Server.

32. Click OK.
33. Briefly review the successfully completed steps in the Executing Package dialog box and then click the Done button.
34. In the SQL Server Enterprise Manager console tree, expand the default instance, expand Databases, expand NorthwindReportData, and then click Tables.
35. In the details pane, right-click TotalValue, point to Open Table, and then click Return All Rows.

The Data In Table ‘TotalValue’ In ‘NorthwindReportData’ On ‘SelfPacedCPU’ window appears displaying the contents of this newly created and populated table.

36. Close the Data In Table ‘TotalValue’ In ‘NorthwindReportData’ On ‘SelfPacedCPU’ dialog box.
37. Do not close SQL Server Enterprise Manager.

Using DTS Designer

To create a new package using DTS Designer, in the SQL Server Enterprise Manager console tree, right-click Data Transformation Services and then click New Package. The method of opening an existing package within the Data Transformation Services container depends on how the DTS package was stored. If the DTS package was saved as a structured storage file, right-click Data Transformation Services, and then click Open Package to open the file from disk. If the DTS package was saved to SQL Server, click the Local Packages container in the console tree (in the Data Services container) and then double-click the DTS package in the details pane. If the DTS package was saved to SQL Server Meta Data Services, click the Meta Data Services container in the console tree (in the Data Services container) and then double-click the DTS package in the details pane.

DTS Designer allows you to graphically create connections to data sources and destinations, configure DTS tasks, perform DTS transformations, and specify precedence constraints. You use the drag-and-drop method and you complete the dialog boxes for objects in order to create DTS packages within the design sheet. Figure 7.15 displays the user interface for DTS Designer.

Figure 7.15  The DTS Designer user interface. (Image unavailable)

When creating a DTS package using DTS Designer, the first step is to select a data source. You can either drag a data source object from the Connection toolbar to the design sheet or select a data source from the Connection menu item. The Connection Properties dialog box that appears varies based on the data source selected. Complete the dialog box to configure the data source. This dialog box is similar to the dialog box displayed by the DTS Import/Export Wizard. Figure 7.16 displays the dialog box for a connection to SQL Server using the Microsoft OLE DB Provider for SQL Server.

Figure 7.16  The Connection dialog box. (Image unavailable)

The next step is to select and configure a data destination in the same manner as described above. Figure 7.17 displays a design sheet consisting of three data sources: two connections to Microsoft OLE DB Provider for SQL Server and one connection to a Text File (Source).

Figure 7.17  Configuring a data destination. (Image unavailable)

Next, you need to define the tasks that you want to occur using either the Task menu or the Task toolbar. If you select the Transform Data task, you are prompted to select the data source and the data destination. If you select any other task, a dialog box will appear to prompt you to configure the properties of the task (such as configuring the Execute SQL Task to create a table using an existing data connection). If you select the Transform Data task, a dark gray arrow appears pointing from the data source to the data destination. If you select any other task, it simply appears in the design sheet as an icon. Figure 7.18 displays two Transform Data tasks and an Execute SQL task that creates a table (in addition to three data connections).

To edit and customize a Data Transformation task, double-click the dark gray arrow between the data source and the data destination to open the dialog box for that task. On the Source tab, if the data source is a database, you can filter the data being copied by selecting specific tables or views or by using a Transact-SQL query. Figure 7.19 displays a Transact-SQL query being used to filter the data being imported.

Figure 7.18   Partial DTS Package without workflow control. (Image unavailable)

Figure 7.19  Using a query to filter the imported data. (Image unavailable)

On the Destination tab, you can define information about the data being imported (such as column definitions). Your choices will vary depending on the data destination. If the data destination is a database, you can create and define a new table or select an existing table for each table being imported.

On the Transformations tab, you can configure custom transformations. By default, the source columns are copied to the destination columns without modification. If you want to modify column data between the data source and the data destination, select the column you want to have modified either in the Name drop-down list or by clicking the arrow between the source and the destination (the arrow between the source and the destination for the selected column will appear bold). Next, click the New button or the Edit button to create a new transformation or modify an existing transformation (double-clicking the black arrow modifies the existing transformation). If you click the New button, you can choose the type of transformation you want from a list of available transformations in the Create New Transformation dialog box. See Figure 7.20.

Figure 7.20  Creating a new transformation. (Image unavailable)

If you select ActiveX Script from the Create New Transformation dialog box, you can create a new transformation script to perform more complex transformations. See Figure 7.21.

On the Lookups tab, you can define a lookup query. A lookup query requires a data connection to run queries and stored procedures against, in addition to a data source and data destination. Use a lookup query to look up tabular information, perform parallel updates on multiple database systems, validate input before loading, invoke stored procedures in response to input conditions, and use global variable values as query parameters.

On the Options tab, you can define a number of additional properties for the transformation. You can define one or more exception files to be used for recording exception records during package execution. This file can be on either a local drive or a network drive. The file can be written in a SQL Server 7.0 format for backward compatibility. You can split source and destination errors into separate files. You can also define a maximum number of errors allowed before the execution of the package will cease. Finally, you can also define specific execution properties when the destination connection is the Microsoft OLE DB Provider for SQL Server. These properties include specifying high-speed bulk-copy processing, constraint checking during package execution, lock type, batch size, and identity insert properties.

Figure 7.21  Creating a new transformation script. (Image unavailable)

Once you have configured the Data Transformation task and any other DTS tasks your DTS package will perform, you must configure precedence constraints. In our simple example, we have data from two separate data sources being copied to a data destination. We also have a Create Table task. You use precedence constraints to determine the order of execution of each task. To establish workflow precedence, select two or more tasks in the order the tasks will execute, and then select the type of workflow from the Workflow menu. For example, if the Create Table task must execute before the data copy to the data destination, select the On Success precedence constraint from the Workflow menu. See Figure 7.22.

You could create a Send Mail task and configure an On Failure precedence constraint between the Create Table task and the Send Mail task. This would send an e-mail notification to an administrator if the Create Table task failed. When you are using fully automated and scheduled DTS packages to perform database operations, failure notification is essential.

Figure 7.22  Selecting the On Success precedence constraint. (Image unavailable)

Practice: Creating a Data Transfer and Transform Package Using DTS Designer

In this practice you create a data transfer and transform package using DTS Designer.

To create a data transfer and transform package using DTS Designer

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain server as Administrator.
2. In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, and then expand the default instance.
3. Right-click Data Transformation Services and then click New Package.

DTS Designer appears.

4. On the Connection menu, click Text File (Source).

The Connection Properties dialog box appears.

5. In the New Connection text box, type New Products.
6. In the File Name text box, type C:\SelfPacedSQL\CH_7\NewData.txt and then click OK.

The Text Files Properties dialog box appears displaying the Select File Format page.

7. Verify that the Delimited Columns Are Separated By Character(s) option button is selected and then click Next.

The Specify Column Delimiter page appears.

8. Verify that the Comma option button is selected and then click the Finish button.

The Connection Properties dialog box reappears.

9. Click OK.

The New Products icon appears on the design sheet.

10. On the Connection menu, click Microsoft OLE DB Provider for SQL Server.

The Connection Properties dialog box appears.

11. In the New Connection text box, type Northwind Report Data.
12. In the Server drop-down list, verify that SelfPacedCPU is selected.
13. In the Database drop-down list, select NorthwindReportData and then click OK.

The New Products and Northwind Report Data icons both appear on the design sheet.

14. On the Task menu, click Transform Data Task.

Your mouse pointer changes and displays the words Select Source Connection and has an arrow attached to it.

15. Click the New Products icon.

Your mouse pointer changes and displays the words Select Destination Connection and has an arrow attached to it.

16. Click the Northwind Report Data icon.

A dark gray arrow appears pointing from the New Products icon to the Northwind Report Data icon.

17. Double-click the dark gray arrow.

The Transform Data Task Properties dialog box appears with the Source tab selected.

18. In the Description text box, type Adding New Products.
19. Click the Destination tab.
20. Click the Create button to create a new table into which to insert data.

The Create Destination Table dialog box appears displaying a CREATE TABLE statement.

21. Delete the entire CREATE TABLE statement.
22. Using Notepad, open the NewProducts.sql file in C:\SelfPacedSQL\CH_7.
23. Copy the contents of this file and paste the contents into the SQL Statement box, and then click OK.

The Transform Data Task Properties dialog box reappears.

24. Click the Transformations tab.

Notice the default mappings of source columns to destination columns. In particular, notice that there are more columns in the destination than there are in the source. The reason is that we have added a column entitled TotalValue, which will be an aggregated column from two existing columns. Also notice that columns 8–10 are incorrectly mapped because of the addition of this new column.

25. Right-click the mapping arrow between Col010 and ReorderLevel and then click the Edit button.

The Transformation Options dialog box appears with the General tab selected.

26. Click the Destination Columns tab.
27. In the Selected Columns list, click the < button to remove ReorderLevel.
28. From the Available Columns list, click Discontinued, click the > button and then click OK.

Notice that the mapping has now changed for Col010 to Discontinued.

29. Right-click the mapping arrow between Col009 and UnitsOnOrder and then click the Edit button.

The Transformation Options dialog box appears with the General tab selected.

30. Click the Destination Columns tab.
31. In the Selected Columns list, click the < button to remove UnitsOnOrder.
32. From the Available Columns list, click ReorderLevel, click the > button and then click OK.

Notice that the mapping has now changed for Col009 to ReorderLevel.

33. Right-click the mapping arrow between Col008 and TotalValue and then click the Edit button.

The Transformation Options dialog box appears with the General tab selected.

34. Click the Destination Columns tab.
35. In the Selected Columns list, click the < button to remove TotalValue.
36. From the Available Columns list, click UnitsOnOrder, click the > but ton and then click OK.

Notice that the mapping has now changed for Col008 to UnitsOnOrder.

37. In the Source column, click Col006, and then press Ctrl and click Col007.

Notice that both Col006 and Col007 are selected.

38. In the Destination column, click TotalValue.
39. Click the New button.

The Create New Transformation dialog box appears.

40. Click ActiveX Script and then click OK.

The Transformation Options dialog box appears with the General tab selected.

41. Click the Properties button.

The ActiveX Script Transformation Properties dialog box appears.

42. Modify the line of code that reads

DTSDestination("TotalValue")=DTSSource("Col006") 

to read

DTSDestination("TotalValue")=DTSSource("Col006")*DTSSource("Col007")

43. Click the Parse button.

A DTS Designer message box appears stating that the ActiveX script was successfully parsed.

44. Click OK.
45. Click the Test button.

A Testing Transformation dialog box appears to test the Transformation and a Package Execution Results message box appears stating that the package was successfully executed.

46. Click OK.
47. Click the Done button in the Testing Transformation dialog box.
48. Click OK in the ActiveX Script Transformation Properties dialog box.

The Transformation Options dialog box reappears.

49. Click OK in the Transformation Options dialog box.

Notice that the mappings now show Col006 and Col007 combined and being mapped to TotalValue.

50. Click OK in the Transform Data Task Properties dialog box.
51. On the Task menu, click Execute SQL Task.

The Execute SQL Task Properties dialog box appears.

52. In the Description text box, type Drop Table.
53. In the Existing Connection drop-down list, click Northwind Report Data.
54. In the SQL Statement box, type DROP TABLE NewProducts and then click OK.

The Drop Table task icon appears on the design sheet.

55. On the Task menu, click Execute SQL Task.

The Execute SQL Task Properties dialog box appears.

56. In the Description text box, type Create Table.
57. In the Existing Connection drop-down list, click Northwind Report Data.
58. Click the Browse button.

The Select File dialog box appears.

59. Using the Look In drop-down list, browse to C:\SelfPacedSQL\CH_7 and then open the NewProducts.sql script.

The saved CREATE TABLE statement appears in the SQL statement box.

60. Click OK.

The Create Table task icon appears on the design sheet.

61. On the Task menu, click Execute SQL Task.

The Execute SQL Task Properties dialog box appears.

62. In the Description text box, type Backup Northwind Report Data.
63. In the Existing Connection drop-down list, click Northwind Report Data.
64. Click the Browse button and then open the BackupNorthwindReportData.sql script in C:\SelfPacedSQL\CH_7.

The saved BACKUP DATABASE statement appears in the SQL statement box. Change the drive path in this script if you are not using the C drive for your SQL Server 2000 program and data files.

65. Click OK.

The Backup Northwind Report Data task icon appears on the design sheet.

66. Click the Drop Table task icon and then press Ctrl and click the Create Table task.

Notice that both task icons are selected.

67. On the Workflow menu, click On Completion.

A blue-and-white striped arrow appears between the Drop Table and the Create Table task icons. The Create Table task will execute whenever the Drop Table task completes, regardless of the success of the Drop Table task. If the NewProducts table does not exist, the Drop Table task will fail and the Create Table task will create the table.

68. Click the Create Table task icon and then press Ctrl and click the New Products icon.
69. On the Workflow menu, click On Success.

A green-and-white striped arrow appears between the Create Table task and the New Products to Northwind Report Data task icons. The New Products to Northwind Report Data task will only execute when and if the Create Table task creates the NewProducts table.

70. Click the Northwind Report Data icon and then press Ctrl and click the Backup Northwind Report Data icon.
71. On the Workflow menu, click On Success.

A green-and-white striped arrow appears between the New Products to Northwind Report Data task icon and the Backup Northwind Report Data task icon. The Backup Northwind Report Data task will only execute when and if the New Products to Northwind Report Data task completes successfully.

72. On the Package menu, click Save.

The Save DTS Package dialog box appears.

73. In the Package Name text box, type Lesson 2 and then click OK.
74. On the toolbar, click the Execute button.

The Executing DTS Package: Lesson 2 dialog box appears displaying the progress of the execution of the DTS package. When the DTS package is complete, a Package Execution Results message box appears stating that the execution of the package was successful.

75. Click OK and then click the Done button.
76. Close DTS Package: Lesson 2 by clicking the Close button for the DTS Designer window.
77. In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance, expand Databases, expand NorthwindReportData, and then click Tables.
78. In the details pane, right-click NewProducts, point to Open Table, and then click Return All Rows. You might need to press F5 to refresh the display.

The Data In Table ‘NewProducts’ In ‘NorthwindReportData’ On ‘SelfPacedCPU’ dialog box appears. Notice that the new table is populated and the aggregated column, TotalValue, exists.

79. Close the Data In Table ‘NewProducts’ In ‘NorthwindReportData’ On ‘SelfPacedCPU’ window.

Do not close SQL Server Enterprise Manager.

Extending DTS Package Functionality

You can extend the capabilities of your DTS packages in a variety of ways. It is beyond the scope of this book to cover all of the ways you can incorporate the plethora and complexity of DTS tasks into DTS packages. However, the following extended functionality deserves specific mention.

Transaction Support

DTS packages support distributed transactions using functions provided by Microsoft Distributed Transaction Coordinator (DTC). To obtain this functionality, DTC must be running on the computer executing the DTS package. Distributed transactions are used to ensure that DTS tasks within a package that occur in parallel are all committed successfully or none are committed. This is particularly useful when a DTS package spans multiple database servers or when multiple DTS packages run under the control of a single DTS package.

Message Queue Task

The Message Queue task allows you to use Message Queuing to send and receive messages between DTS packages. These messages can be text, files, or global variables and their values. Message queuing allows these messages to be sent when the destination DTS package is unavailable (such as when various laptop computers are disconnected). When a destination DTS package becomes available, the originating DTS package continues until complete. Other available options include sending data files to the computer responsible for processing them, distributing files throughout the enterprise, and splitting a large job into several component parts and then parceling them out to different computers in the
network.

To use the Message Queue task, you must install Message Queuing server software on your network and Message Queuing client software on the computer running the Message Queue task. For Windows 2000, you install Message Queuing server software on a Windows 2000 domain controller running Active Directory. Thereafter, you can install Message Queuing client software on your computer. For Windows NT, you install MSMQ from the Windows NT 4.0 Option Pack.

Send Mail Task

The Send Mail task allows a DTS package to notify an administrator about its progress (such as the success or failure of a database backup). The Send Mail task can also send attachments, which can consist of dynamically updated files. The Send Mail task requires the installation of Messaging Application Programming Interface (MAPI) on the local computer with a valid user profile for the domain user account used by the SQL Server service.

Programming Templates

SQL Server 2000 also ships with a wide variety of programming samples for Visual C++ and Visual Basic. These samples are located on the SQL Server 2000 compact disk in the \DevTools\Samples\Dts folder in a self-extracting zip file and can be installed during a Custom setup (by default to the C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\Dts folder).

Lesson Summary

You can use the DTS Import/Export Wizard to copy and transform data and database objects. It is most useful for copying data and database objects between one data source and one data destination with minimal transformations. Use DTS Designer for more complex transformation packages. DTS Designer allows you to create packages to and from multiple data sources and destinations using workflow logic, message queuing, and transaction control. You can also use Visual Basic and Visual C++ to extend the complexity and more finely control the workflow logic of DTS packages.

Lesson 4: Working with DTS Packages

Now that you understand how to create and execute a DTS package, you will learn about storing and securing DTS packages. In addition, you will learn additional methods for executing a saved DTS package, as well as using DTS package logs to troubleshoot problems that occur during the execution of a DTS package. You will also learn about editing DTS packages when data sources and destinations are unavailable. Finally, you will learn about browsing and sharing meta data about DTS packages.

• Understand the different storage options for DTS packages
• Secure a DTS package
• Execute DTS packages from the command prompt
• Edit a DTS package when a data source or destination is unavailable

Estimated lesson time: 15 minutes

Understanding DTS Package Storage Options

As discussed earlier, you can store a DTS package using SQL Server 2000, SQL Server Meta Data Services, a structured storage file, or a Visual Basic file. You use each storage format to accomplish different objectives, based on their capabilities.

SQL Server 2000

Saving a DTS package to SQL Server 2000 allows you to save a DTS package as a binary object in the sysdtspackages table in the msdb database on any SQL Server 2000 instance in your organization. You can keep an inventory of DTS packages in one location on your network. Each version of a DTS package is saved, preserving the development history of a package. This allows you to retrieve and edit any version of a DTS package you choose, not just the most recent version.

When you save a DTS package to SQL Server 2000, you can set both an owner password and a user password. These passwords are used in addition to the Windows Authentication or SQL Server Authentication used to connect to SQL Server 2000. Users with access to the user password can run a DTS package, but cannot open or edit a package. Users must have access to the owner password to open or edit a DTS package. This extra level of security is strongly recommended, particularly on a SQL Server 2000 instance that permits SQL Server Authentication.

Meta Data Services

Saving a DTS package to SQL Server Meta Data Services allows you to track package version, meta data, and data lineage (original data source and transformations) information. You can view version history for packages saved to Meta Data Services in SQL Server Enterprise Manager and can open the version you want. DTS uses the DTS Information Model to store meta data transformation information in Meta Data Services. The DTS Information Model describes the data transformations, how they are grouped, and the types of data accessed. This meta data information can be stored, browsed, and reused.

Saving a DTS package in Meta Data Services allows you to record and track two types of data lineage, row-level and column-level. Row-level data lineage records the source of the data in each row and the transformations that were applied to it. This is useful for providing an audit trail of package execution and row-level transformations. You must specifically enable row-level data lineage by creating a column in each row to contain a lineage identifier. Column-level data lineage provides information regarding the package version and the database tables and columns used. You can browse packages and versions to determine whether any package saved in Meta Data Services uses a particular table or column. This can be particularly useful if a data source is later determined to be of questionable value (such as corrupt or inaccurate data). You enable column-level data lineage by scanning and saving the meta data once a DTS package has been saved to Meta Data Services.

Structured Storage File

Saving a DTS package to a structured storage file allows you to copy, move, and send a package across the network (such as in a mail message) without storing the package in a database or a repository. Multiple versions and multiple packages can be stored within a single file. Saving to a structured storage file also supports owner and user passwords. You can use the command-prompt DTS package execution utilities to execute a DTS package saved as a structured storage file. A DTS package saved as a structured storage file has a .DTS extension.

Visual Basic File

Saving a DTS package to a Visual Basic file allows the DTS package to be edited using Visual Basic or Visual C++. This allows developers to incorporate DTS packages into Visual Basic programs or used as prototypes to reference the components of the DTS object model. A DTS package saved as a Visual Basic file cannot be reopened and edited with DTS Designer.

Using DTS Package Execution Utilities

DTS provides two command-prompt package execution utilities: the DTS Run utility and the Dtsrun command. Using either of these utilities, a user can execute a DTS package without opening it. If the DTS package was saved using an owner or a user password, you must provide the appropriate password. If the DTS package was saved to a structured file, you must specify the filename. If the DTS package was saved to SQL Server, you must specify connection information to the SQL Server instance containing the DTS package.

DTS Run Utility

The DTS Run Utility is an interactive utility that allows you to connect to a server or specify a file, specify scheduling options, identify and enable an event log, add new global variables and change the properties of existing variables, and create a Dtsrun command with either clear or encrypted arguments for later use. To access the DTS Run utility, execute Dtsrunui from a command prompt.

Dtsrun

The Dtsrun command allows you to run a DTS package from a command prompt using arguments and embed this command in a batch file. For example, to execute the DTS package named MyPackage saved to C:\DTSPackages\DTS1.dts with an owner password of Password, you would type the following command:

dtsrun /FC:\DTSPackages\DTS1.dts /NMyPackage /MPassword

Using DTS Package Logs and Exception Files

DTS records information about the success or failure of each step in the execution of a package in the DTS package log. This includes start and end times for each step and the length of execution. If a step was not run, this is also recorded. Package logging occurs only when SQL Server 2000 is running on a Windows 2000 server. DTS also uses DTS exception files to save error information about rows of data that were not copied and to save the actual source and destination rows that failed.

Performing Disconnected Edits

You can use DTS Designer to open and edit an existing DTS package. Normally, connectivity to each data source and destination is required to protect against setting invalid properties. However, sometimes those connections are not available. DTS Designer contains a Disconnected Edit feature that allows you to modify a DTS package when you cannot connect to the original data sources and destinations (such as a DTS package created on a test system for use on a production system in a different site). You can also use this feature to view and modify properties that are not exposed through DTS Designer (such as task names, step names, and connection names).

Lesson Summary

DTS packages can be stored in several different ways. Storing a DTS package either in SQL Server 2000 or in a structured storage file allows you to set a user password as well as an owner password. This allows a user to run a DTS package from a command prompt using one of the DTS package execution utilities without being able to view or edit the package. However, storing a DTS package using SQL Server Meta Data Services allows extensive row-level and column-level tracking of data lineage and transformation meta data information. This is particularly useful when data is being imported and transformed from a wide variety of sources. Finally, DTS Designer provides the Disconnected Edit feature, which allows you to edit a DTS package when the underlying data connections are unavailable.

Lesson 5: Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement

The BULK INSERT Transact-SQL statement and the Bcp command-prompt utility are used to import data into SQL Server 2000 from a text file. These methods are designed to efficiently transfer large amounts of data. In this lesson you will learn how to use each of these commands.

• Describe how SQL Server 2000 copies data in bulk using text files
• Describe how to optimize bulk copy operations
• Use the Bcp command-prompt utility to import and export data
• Use the BULK INSERT Transact-SQL statement to import data

Estimated lesson time: 30 minutes

Copying Data in Bulk Using Text Files

In addition to using DTS, SQL Server 2000 can import data from text files using the BULK INSERT Transact-SQL statement or using the Bcp command-prompt utility. Each is designed to import large quantities of data with minimal transformation during the process at very high speed. The BULK INSERT statement is generally faster. The Bcp program has been used for many years (DTS is a recently introduced tool), and veteran database administrators frequently have generated numerous scripts that they use to import data. For this reason, you need to understand Bcp. SQL Server 2000 supports all of those existing Bcp scripts. However, if you need to create new scripts, DTS is much easier to use. It has identical performance because the BULK INSERT statement has been encapsulated in the Bulk Insert task and the graphical interface painlessly walks you through the formatting issues.

The text files are generally tab-delimited files (but other delimiters can also be used, such as commas). You must specify the format of this file during the import process. You can specify the format as part of the Bcp command or BULK INSERT statement. You can also specify it in a reusable format file. The text files you will use to import from are frequently generated by earlier versions of SQL Server, by other database programs, or by spreadsheets. The data in the text file can be stored in character mode or in binary mode (for SQL Server to SQL Server transfers).

Using Bcp

When using Bcp to import data from a data file, you must specify each parameter. The Bcp syntax is quite cryptic and must be entered precisely or the command will fail. Table 7.7 lists the more commonly used parameters. Bcp parameters are case-sensitive.

The following example imports data from the NewData text file to the NewData table on New Database with a column delimiter of a comma, a row delimiter of a new line, using character data, with a batch size of 250, an error size of 50, using the NewData.err error file, connecting using a trusted connection, and specifying a TABLOCK hint:

bcp NewDatabase..NewData in C:\SelfPacedSQL\CH_7\NewData.txt –c
 –t"," –r\n /e C:\SelfPacedSQL\CH_7\NewData.err –b250 –m50 –T –h "TABLOCK"

Table 7.7  Commonly Used Parameters for Bcp

Argument	Description
Database_name	The database into which the data is being inserted. If not specified, the default database for the specified user is used.
Table_name	The name of the table into which the data is being inserted or from which the data is copied.
"Query"	The query used to filter the data being copied out of SQL Server.
In | Out	The direction of the bulk copy operation.
Format	Used to create a format file.
Data_file	The data file used as the source or the destination of the bulk copy operation.
-m	The maximum number of errors that can occur before the bulk copy operation is cancelled. Default is 10.
-f	Specifies the full path of the format file. This parameter is optional.
-e	Specifies the full path of the error file used to record all rows Bcp is unable to transfer to the database. If this option is not used, no error file is created.
-b	Specifies the number of rows per batch of data copied. Each batch is copied to the SQL Server 2000 instance as a single transaction.
-c	Specifies the bulk copy operation using a character data type.
-t	Specifies the field terminator. The default is tab.
-r	Specifies the row terminator. The default is new line.
-S server_name	Specifies the server name (and instance name if applicable) to which
[/instance_name]	Bcp will connect. Default instance on the local server is the default.
-U	Specifies the login ID.
-P	Specifies the password for the login ID. NULL is the default.
-T	Specifies the use of a trusted connection, using the security credentials of the current user.
-h "hint"	Hints such as TABLOCK, ROWS_PER_BATCH=nn and ORDER ASC | DESC. These hints tell SQL Server how to process the imported data most efficiently.

Practice: Importing Data Using Bcp

In this practice you import data to SQL Server 2000 using the Bcp command-prompt utility.

To import data using Bcp

1. Ensure that you are logged on to the SelfPacedSQL.MSFT domain server as Administrator.
2. Click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

SQL Server Analyzer appears displaying the the Connect To SQL Server dialog box.

3. In the Connect To SQL Server dialog box, select SelfPacedSQL from the SQL Server combo box, and use Windows authentication.
4. Click OK.
5. On the File menu, click Open.

The Open Query File dialog box appears.

6. Select NewData.sql in the C:\SelfPacedSQL\CH_7 folder.

A CREATE TABLE statement appears that will create a table called NewData into which you will import data using the Bcp command and bulk copy data.

7. Click the Execute Query button on the toolbar to execute the NewData.sql statement.
8. Click Start, point to Programs, point to Accessories, and then click Command Prompt.

A Command Prompt window appears.

9. Type bcp and press Enter.

Notice the display of available arguments.

10. Type the following command on a single line:

bcp NorthwindReportData..NewData in C:\SelfPacedSQL\CH_7\NewData.txt –c
 –t"," –r\n /e C:\SelfPacedSQL\CH_7\NewData.err –b250 –m50 –T –h
 "TABLOCK"

---

NOTE:
You must enter the code in the preceding example as a single line without a line break.

---

11. Press Enter.

Notice that 1343 rows are copied in batches of 250 rows. The packet size used and the elapsed clock time are also displayed. An empty NewData.err file now appears in the C:\SelfPacedSQL\CH_7 folder.

12. Close the Command Prompt window.
13. In SQL Query Analyzer, click the Clear Window button on the toolbar.
14. In the Query pane, type SELECT * FROM NewData.
15. Click the Execute Query button on the toolbar.

Notice that 1343 rows are displayed from the NewData table.

16. Close SQL Query Analyzer.

Using the BULK INSERT Transact-SQL Statement

The BULK INSERT Transact-SQL statement requires information similar to that required by the Bcp command to perform a bulk copy operation. The following example uses identical information to that used in the preceding Bcp example, except using Transact-SQL.

BULK INSERT NewDatabase..NewData
FROM 'C:\SelfPacedSQL\CH_7\NewData.txt' 
WITH
(
BATCHSIZE = 250 ,
DATAFILETYPE = 'char' ,
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n',
MAXERRORS = 50 ,
TABLOCK            )

Optimizing Bulk Copy Operations

When you perform a bulk copy operation that imports data into SQL Server 2000, the recovery mode of the destination database affects performance. If the database receiving the data is set for full recovery, all row insertions are fully logged. This generates a substantial number of log records during a large bulk copy operation, which can fill up the transaction log and negatively affect performance. For optimum performance during a bulk copy operation, setting the database to bulk-logged recovery is recommended. When you use bulk-logged recovery, the transaction log captures the results of bulk operations, but does not capture row-by-row insertions.

In addition, if you are loading a large amount of data from a single client into an empty table, you should specify the TABLOCK hint (rather than having SQL Server 2000 start with row locks and escalate them to table locks) and use a large batch size using the ROWS_PER_BATCH hint (large batch sizes are generally more efficient than small batch sizes). If the table into which you are importing the data has indexes, it is generally faster to drop all nonclustered indexes and re-create them after the data insertion. If the table has clustered indexes, it is generally faster to order the data in the text file to match the order in the clustered index and specify the ORDER hint.

If the table into which you are importing data is not empty and contains indexes, the decision on whether to drop indexes depends upon the amount of data being inserted compared to the amount of data existing in the table. The greater the percentage of new data, the faster it is to drop all indexes and re-create them after the data is loaded.

Lesson Summary

In addition to DTS, SQL Server 2000 provides the command-prompt utility Bcp and the Transact-SQL BULK INSERT statement for importing data from text files. These methods are particularly suited to high-speed insertions of data into a database. Bcp has been used for years as the only method for inserting large amounts of data into SQL Server, and many existing scripts will be in use for many more years. Database administrators must be familiar with Bcp to work with existing scripts (even if the database administrator is not creating any new scripts). The BULK INSERT statement can be used from within SQL Query Analyzer, and it is encapsulated within the DTS Bulk Insert task. There are a number of ways to optimize the speed of insertions of large amounts of data using these methods, including using bulk-logged recovery and dropping nonclustered indexes.

Review

Here are some questions to help you determine whether you have learned enough to move on to the next chapter. If you have difficulty answering these questions, review the material in this chapter before beginning the next chapter. The answers for these questions are located in the Appendix, "Questions and Answers."

1. You are analyzing the data in a text file containing data that you want to import into your database. You have determined that the data is internally consistent, but contains fields that are inconsistent with existing data in your database. The text file is representative of data that you will be importing weekly. What is your best solution for achieving the necessary data consistency? You have already determined that you cannot change the original data source.
2. Describe the difference between the On Success precedence constraint and the On Completion precedence constraint.
3. You have created and saved a simple data import and transform package that imports data from the spreadsheets maintained by your salespeople for expense reports. However, you want to add additional functionality to the package, including notifying an administrator after the entire sales staff has uploaded their expense reports. How might you accomplish this?
4. You have created a package that collects completed sales information from several different spreadsheet files used by salespeople in your company. After it collects this information, it inserts the collected information in one of your sales reporting databases. You want to distribute this package to your salespeople to execute regularly as part of their weekly reports. However, you do not want them to be able to open or edit the package. How should you save this package and how should you secure it?
5. You want to import a large amount of data from a text file into a table that contains a clustered and a nonclustered index. The data being inserted exists in the text file in the same order as the clustered index. As part of the process, you first truncate the existing table to replace it with this new data. Should you drop each of the indexes before you insert the new data?