Chapter 4: Creating Advanced DTS Tasks

Overview

In this chapter, you will learn how to:

• Perform lookup queries inside a Transform Data task

• Use a Data Driven Query task to determine which of several queries is used to insert, update, or delete data at the destination data store

• Access the phases of the multiphase data pump

• Call a subpackage using an Execute Package task

In Chapter 1 and Chapter 2, you learned how to use a number of tasks, including the Transform Data, Execute SQL, the Bulk Insert, and the Transfer SQL Server Objects tasks. In this and the next few chapters, you will learn how to extend these tasks and how to use additional tasks to work toward completing the prototype of a data movement application.

Note  

In Chapter 1, though you worked with them, you didn t get to see several of these task types by name . This is because they were created and used through the DTS Import/Export Wizard. In Chapter 2, using DTS Designer, you were able to see the wizard-created Transform Data tasks, and you also directly created your own Transform Data tasks in the PopulateTimeDimension package.

The following list describes the 19 pre-built tasks that ship with DTS that you can use to build data movement applications. You ve already learned about a few of these and you ll learn about many of these throughout the remainder of this book as you build a prototype of a data movement application.

Task	Description
Data movement tasks
Transform Data task	This task enables a DTS package to transform data during insert operations between one or more OLE DB “compliant data sources and destinations. You learned about this task in Chapters 1 and 2, and you added this task to the LoadHistoricalData package in Chapter 2. You will use this task again in this chapter in the UpdateCustomerDim package and again in Chapter 9 in the UpdateSalesFacts package.
Data Driven Query task	This task scans through data in a source rowset one row at a time and then executes a particular SQL statement based on the data in that row. This task can be slow but is very powerful. You will learn how to use this task in this chapter as you add it to the UpdateProductDim package.
Bulk Insert task	This task copies data at high speed from a text file to SQL Server using the Transact-SQL BULK INSERT statement. You learned about this task in Chapter 2 when you added it to the LoadHistoricalData package.
Programming tasks
Execute SQL task	This task executes any Transact-SQL statement inside a package. You learned about this task in Chapters 1 and 2 and will incorporate it into every package you build throughout this book to perform a variety of functions.
ActiveX Script task	This task executes any ActiveX script inside a package. You will learn about using the ActiveX Script task in Chapter 7 as you add functionality to each package you have previously built.
Execute Package task	This task enables you to divide a package into logical components for ease of programming. You will learn about this task in this chapter and use it in the MasterUpdate package to execute the UpdateCustomerDim and UpdateProductDim packages. You will use it again in Chapter 9 to execute the UpdateSalesFacts package from the MasterUpdate package.
Execute Process task	This task executes any Windows-compatible program or batch file. This task is discussed in Chapter 5 of this book but is not incorporated into any of the packages you will build.
Dynamic Properties task	This task sets DTS object property values at run time. The values can be retrieved from a number of different sources. You will learn how to use this task in Chapter 6, and you ll add this task to every package in the data movement application.
Specialized tasks
Send Mail task	This task sends e-mail to a recipient from within a DTS package. This task is not covered in this book.
File Transfer Protocol task	This task receives files from an FTP server from within a package using the File Transfer Protocol (FTP). This task is not covered in this book.
Message Queue task	This task communicates asynchronously with applications (including other packages) through Microsoft Message Queuing Services from within a package. This task enables a package to send or receive messages, or to send files between packages and pause until they are received. This task is not covered in this book.
Database maintenance tasks
Copy SQL Server Objects task	This task copies SQL Server objects, such as views, stored procedures, defaults, and user -defined functions, between SQL Server databases. You learned about this task in Chapter 1. This task is not incorporated into the packages that comprise the prototype of a data movement application.
Transfer Databases task	This task transfers an entire database between SQL Server instances and is used by the Copy Database Wizard. This task is not covered in this book.
Transfer Logins task	This task copies logins between SQL Server instances and is used by the Copy Database Wizard. This task is not covered in this book.
Transfer Master Stored Procedures task	This task copies stored procedures stored in the master database to another SQL Server instance and is used by the Copy Database Wizard. This task is not covered in this book.
Transfer Jobs task	This task copies jobs between SQL Server instances and is used by the Copy Database Wizard. This task is not covered in this book.
Transfer Error Messages task	This task copies user-defined error messages between SQL Server instances and is used by the Copy Database Wizard. This task is not covered in this book.
Data warehouse tasks
Analysis Services Processing task	This task processes objects within Analysis Services. You will learn about this task in Chapter 9 and add this task to the MasterUpdate package.
Data Mining Prediction task	This task runs prediction queries in data mining models within Analysis Services. This task is not covered in this book.

As you will see throughout the remaining chapters, these built-in tasks can be used to solve most of the problems you will face in building a data movement application.

Important  

If the built-in tasks do not provide the functionality you require, you can build your own custom tasks using Microsoft Visual Basic or C++. Building a custom task requires knowledge of one of these programming languages along with an understanding of the DTS Custom Task object model. Building a custom task is beyond the scope of this book. For more information about building a DTS custom task, see Building a DTS Custom Task at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspcust_5kc3.asp .

In this chapter, you will learn to extend the Transform Data task to perform lookup queries so that you can include data from a second data store during a transformation. Lookup queries are similar to Transact-SQL joins and can be performed in both the Transform Data task and the Data Driven Query (DDQ) task. A lookup query is particularly useful when it is not practical to perform a Transact-SQL join with the second data store using a distributed query. For best performance, however, you should use a distributed query whenever possible.

The DDQ task enables DTS to evaluate each row in a rowset and execute one of several different SQL statements based on the content of each row. The DDQ task provides great flexibility, but it can be costly in terms of performance. However, if used properly, the performance penalty can be minimized.

The engine that handles the movement and transformation of data for the Transform Data and Data Driven Query tasks is the multiphase data pump. In the tasks you have learned about so far, you have been using the default entry point to access the DTS data pump. There are additional entry points to the data pump that enable you to customize the behavior of the data pump engine and provide increased functionality to your packages, including customized error handling, data aggregation, and row-level restartability. You will use this functionality in the Data Driven Query task that you will add to the UpdateProductDim package in this chapter and also in Chapter 8.

The final task you will learn to use in this chapter is the Execute Package task. This task enables one package to call another package (called a subpackage) and send parameters to the subpackage to control its execution. The Execute Package task enables you to modularize the components of your data movement application so that a master package begins the execution of the data movement application and each subpackage performs a specific function within the data movement application and is executed when it is needed. For example, in the prototype you are building, you will create a separate package for each dimension and each fact table. Each package will perform all tasks related to its corresponding dimension or fact table, including updates of staging and dimension tables and deletes from the staging tables. You will also create a master package that will execute each subpackage at the appropriate time. This modular design makes it easier to understand the function of each package and also makes it easier to extend the data movement application by simply adding a new dimension update package when a new dimension is added to the Sales cube.