Microsoft SQL Server 2000 Data Transformation Services- P4

Microsoft SQL Server 2000 Data Transformation Services- P4: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for movingdata. It’s easy to use, especially when you’re creating transformation packages with the DTSWizard. But it’s also a flexible tool that you can customize to gain a high degree of controlover the transformation of your data.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2000 Data Transformation Services- P4 DTS Connections and the Data Transformation Tasks126 PART II As indicated by its name, the Transform Data task is at the heart of Data Transformation Services. This task is a data pump that moves data from a data source to a data destination, giving you the opportunity to modify each record as you move it. Three chapters of this book are devoted to the Transform Data task: • This chapter outlines the task’s basic functionality and properties. • Chapter 7, “Writing ActiveX Scripts for a Transform Data Task,” describes the use of ActiveX scripts to programmatically control data transformations. This chapter also dis- cusses creating and using lookups. • Chapter 9, “The Multiphase Data Pump,” shows how to use the new SQL Server 2000 capability to write code for eight different events in the operation of the Data Pump. There are also chapters devoted to the other two data transformation tasks: • Chapter 8, “The Data Driven Query Task,” describes a task that can define several output queries in the process of data transformation. • Chapter 10, “The Parallel Data Pump Task,” describes a new task that lets the data pump use hierarchical recordsets. Additional key information relating to the Transform Data task can be found in these chapters: • Chapter 5, “DTS Connections” • Chapter 27, “Handling Errors in a Package and Its Transformations” • Chapter 28, “High Performance DTS Packages” • Chapter 32, “Creating a Custom Transformation with VC++” NOTE It’s possible to get confused about the naming of the Transform Data task. Some peo- ple refer to it as the Data Pump task, reflecting the DataPumpTask and DataPumpTask2 objects that implement this task. It is also called the Data Transformation task. When to Use the Transform Data Task I have built DTS packages that don’t have any Transform Data tasks, and I have built other packages in which this task did all the movement and manipulation of the data. The Transform Data task is one of the most versatile of all the DTS tasks. Many of the others have limitations that prevent them from being used in certain circumstances. The Transform Data task can be used with a variety of data sources and destinations, it delivers high perfor- mance, and you can manipulate data in a very precise way. The Transform Data Task 127 CHAPTER 6I decide whether or not to use the Transform Data task by going through a process of elimina- 6tion. If another task will do the job better, I choose it. If I can’t use any of the other tasks THE TRANSFORMbecause of their limitations, I use the Transform Data task. DATA TASKConsider these specialized situations where other tasks are more effective: • If you are transferring whole databases from SQL Server 7.0/2000 to SQL Server 2000, use the Transfer Databases task. • If you are transferring database objects (tables, views, stored procedures, and so on) from a SQL Server 7.0/2000 database to a SQL Server 7.0/2000 database, use a Transfer SQL Server Objects task. • If you need to choose between several queries when transforming each row of data, con- sider using the Data Driven Query task. (But the Transform Data task in SQL Server 2000 now allows you to modify data using lookups, which removes some of the Data Driven Query task’s advantage in this area.) • If your data source is a text file, your data destination is SQL Server, you are not trans- forming the data as it’s being imported, and you want the fastest possible speed for your data movement, use the Bulk Insert task. • If you are moving data between tables in the same type of relational database, consider using an Execute SQL task. It will be faster than the Transform Data task, but you lose the flexibility of row-by-row processing. • If you are moving hierarchical rowsets, take advantage of the new Parallel Data Pump task. • If you need to move data files to another location, use the FTP task.In all other cases, use the Transform Data task to transform your data. TIP When I was first learning DTS development, I used the Transform Data task a lot more than I ...