Lesson 3: Transferring and Transforming Data with DTS Graphical Tools | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

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.

After this lesson, you will be able to

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.

Figure 7.1

Selecting the data source in the DTS Import/Export Wizard.

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.

Figure 7.2

Specifying a text file as the data source.

figure 7.3 - selecting the file format, field type, and text qualifier.

Figure 7.3

Selecting the file format, field type, and text qualifier.

figure 7.4 - specifying the column delimiter.

Figure 7.4

Specifying the column delimiter.

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.

Figure 7.5

Selecting a destination for your data.

figure 7.6 - specifying the name and properties of the new database.

Figure 7.6

Specifying the name and properties of the new database.

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.

Figure 7.7

Specifying the type of copy operation.

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.

Figure 7.8

Selecting source tables and views.

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.

Figure 7.9

Changing the column mappings and transformations.

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.

Figure 7.10

Specifying transformation options.

figure 7.11 - creating a script using the query designer.

Figure 7.11

Creating a script using the Query Designer.

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.

Figure 7.12

Modifying the column mappings and transformations for your query.

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.

Figure 7.13

Selecting objects to copy.

figure 7.14 - choosing to save or schedule the package.

Figure 7.14

Choosing to save or schedule the package.

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

Ensure that you are logged on to the SelfPacedSQL.MSFT domain server as Administrator.
Click Start, point to Programs, point to Microsoft SQL Server, and then click Import And Export Data.
The DTS Import/Export Wizard appears.
Click Next.
The Choose A Data Source page appears.
In the Data Source drop-down list, verify that the selected data source is Microsoft OLE DB Provider for SQL Server.
In the Server drop-down list, select SelfPacedCPU\MyNamedInstance.
Verify that the Use Windows Authentication option button is selected.
In the Database drop-down list, select Northwind and then click Next.
The Choose A Destination page appears.
In the Destination drop-down list, verify that the selected data destination is Microsoft OLE DB Provider for SQL Server.
In the Server drop-down list, select SelfPacedCPU.
Verify that the Use Windows Authentication option button is selected.
In the Database drop-down list, select <New>.
The Create Database dialog box appears.
In the Name text box, type NorthwindReportData and then click OK.
The Choose A Destination page reappears displaying the new database.
Click Next.
The Specify Table Copy Or Query page appears.
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.
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.
Click Next.
The Save, Schedule, And Replicate Package page appears.
Verify that the Run Immediately check box is selected.
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.
In the Name text box, type NorthwindTableCopy and then click Next.
The Completing The DTS Import/Export Wizard page appears.
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.
Click OK.
In the Executing Package page, briefly review the successfully completed steps and then click the Done button.
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

Right-click NorthwindReportData, point to All Tasks, and then click Import Data.
The DTS Import/Export Wizard appears.
Click Next.
The Choose A Data Source page appears.
In the Data Source drop-down list, verify that the selected data source is Microsoft OLE DB Provider for SQL Server.
In the Server drop-down list, select SelfPacedCPU.
Verify that the Use Windows Authentication option button is selected.
In the Database drop-down list, select Northwind and then click Next.
The Choose A Destination page appears.
In the Destination drop-down list, verify that the selected data destination is Microsoft OLE DB Provider for SQL Server.
In the Server drop-down list, verify that SelfPacedCPU is selected.
Verify that the Use Windows Authentication option button is selected.
In the Database drop-down list, verify that NorthwindReportData is selected and then click Next.
The Specify Table Copy Or Query page appears.
Select the Use A Query To Specify The Data To Transfer option button and then click Next.
The Type SQL Statement page appears.
Click the Browse button.
The Open dialog box appears.
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.
Click Next.
The Select Source Tables And Views page appears.
Click the Results cell in the Destination column and type TotalValue.
Make sure you type TotalValue as a single word with no spaces.
Click the Preview button.
The View Data dialog box appears displaying the results of the query.
Click OK.
The Select Source Tables And Views page reappears.
Click the ellipsis in the Transform column.
The Column Mappings And Transformations dialog box appears.
Click the Edit SQL button.
The Create Table SQL Statement dialog box appears.
Review the Transact-SQL statement.
Notice that the TotalValue column allows nulls.
Click OK to close the Create Table SQL Statement dialog box.
The Column Mappings And Transformations dialog box reappears.
In the Mappings grid, clear the Nullable check box for the TotalValue row.
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.
Click OK to close the Create Table SQL Statement dialog box.
Click OK to close the Column Mappings And Transformations dialog box.
The Select Source Tables And Views page reappears.
Click Next.
The Save, Schedule, And Replicate Package page appears.
Verify that the Run Immediately button is selected.
Select the Save DTS Package check box.
Select the SQL Server Meta Data Services option button, and then click Next.
The Save DTS Package page appears.
In the Name text box, type NorthwindTableTransform and then click Next.
The Completing The DTS Import/Export Wizard page appears.
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.
Click OK.
Briefly review the successfully completed steps in the Executing Package dialog box and then click the Done button.
In the SQL Server Enterprise Manager console tree, expand the default instance, expand Databases, expand NorthwindReportData, and then click Tables.
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.
Close the Data In Table 'TotalValue' In 'NorthwindReportData' On 'SelfPacedCPU' dialog box.
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.

Figure 7.15

The DTS Designer user interface.

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.

Figure 7.16

The Connection dialog box.

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.

Figure 7.17

Configuring a data destination.

Note

A connection to a text file specifies that the text file is either a data source or a data connection.

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.

Figure 7.18

Partial DTS Package without workflow control.

figure 7.19 - using a query to filter the imported data.

Figure 7.19

Using a query to filter the imported data.

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.

Figure 7.20

Creating a new transformation.

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.

Figure 7.21

Creating a new transformation script.

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.

Figure 7.22

Selecting the On Success precedence constraint.

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

Ensure that you are logged on to the SelfPacedSQL.MSFT domain server as Administrator.
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.
Right-click Data Transformation Services and then click New Package.
DTS Designer appears.
On the Connection menu, click Text File (Source).
The Connection Properties dialog box appears.
In the New Connection text box, type New Products.
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.
Verify that the Delimited Columns Are Separated By Character(s) option button is selected and then click Next.
The Specify Column Delimiter page appears.
Verify that the Comma option button is selected and then click the Finish button.
The Connection Properties dialog box reappears.
Click OK.
The New Products icon appears on the design sheet.
On the Connection menu, click Microsoft OLE DB Provider for SQL Server.
The Connection Properties dialog box appears.
In the New Connection text box, type Northwind Report Data.
In the Server drop-down list, verify that SelfPacedCPU is selected.
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.
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.
Click the New Products icon.
Your mouse pointer changes and displays the words Select Destination Connection and has an arrow attached to it.
Click the Northwind Report Data icon.
A dark gray arrow appears pointing from the New Products icon to the Northwind Report Data icon.
Double-click the dark gray arrow.
The Transform Data Task Properties dialog box appears with the Source tab selected.
In the Description text box, type Adding New Products.
Click the Destination tab.
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.
Delete the entire CREATE TABLE statement.
Using Notepad, open the NewProducts.sql file in C:\SelfPacedSQL\CH_7.
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.
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.
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.
Click the Destination Columns tab.
In the Selected Columns list, click the < button to remove ReorderLevel.
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.
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.
Click the Destination Columns tab.
In the Selected Columns list, click the < button to remove UnitsOnOrder.
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.
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.
Click the Destination Columns tab.
In the Selected Columns list, click the < button to remove TotalValue.
From the Available Columns list, click UnitsOnOrder, click the > button and then click OK.
Notice that the mapping has now changed for Col008 to UnitsOnOrder.
In the Source column, click Col006, and then press Ctrl and click Col007.
Notice that both Col006 and Col007 are selected.
In the Destination column, click TotalValue.
Click the New button.
The Create New Transformation dialog box appears.
Click ActiveX Script and then click OK.
The Transformation Options dialog box appears with the General tab selected.
Click the Properties button.
The ActiveX Script Transformation Properties dialog box appears.

Modify the line of code that reads

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

to read

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

Click the Parse button.
A DTS Designer message box appears stating that the ActiveX script was successfully parsed.
Click OK.
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.
Click OK.
Click the Done button in the Testing Transformation dialog box.
Click OK in the ActiveX Script Transformation Properties dialog box.
The Transformation Options dialog box reappears.
Click OK in the Transformation Options dialog box.
Notice that the mappings now show Col006 and Col007 combined and being mapped to TotalValue.
Click OK in the Transform Data Task Properties dialog box.
On the Task menu, click Execute SQL Task.
The Execute SQL Task Properties dialog box appears.
In the Description text box, type Drop Table.
In the Existing Connection drop-down list, click Northwind Report Data.
In the SQL Statement box, type DROP TABLE NewProducts and then click OK.
The Drop Table task icon appears on the design sheet.
On the Task menu, click Execute SQL Task.
The Execute SQL Task Properties dialog box appears.
In the Description text box, type Create Table.
In the Existing Connection drop-down list, click Northwind Report Data.
Click the Browse button.
The Select File dialog box appears.
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.
Click OK.
The Create Table task icon appears on the design sheet.
On the Task menu, click Execute SQL Task.
The Execute SQL Task Properties dialog box appears.
In the Description text box, type Backup Northwind Report Data.
In the Existing Connection drop-down list, click Northwind Report Data.
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.
Click OK.
The Backup Northwind Report Data task icon appears on the design sheet.
Click the Drop Table task icon and then press Ctrl and click the Create Table task.
Notice that both task icons are selected.
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.
Click the Create Table task icon and then press Ctrl and click the New Products icon.
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.
Click the Northwind Report Data icon and then press Ctrl and click the Backup Northwind Report Data icon.
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.
On the Package menu, click Save.
The Save DTS Package dialog box appears.
In the Package Name text box, type Lesson 2 and then click OK.
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.
Click OK and then click the Done button.
Close DTS Package: Lesson 2 by clicking the Close button for the DTS Designer window.
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.
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.
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.

Note

There are two types of message queuing, transactional and non-transactional. Using transactional message queuing provides assurance that each message is delivered only once.

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.