SQL Server 2000 DTS Part 8 - DTS Designer Tasks - Transform Data Task
January 23, 2004
A few more DTS Designer tasks are left to cover out of a total of 17 existing in SQL Server 2000 Data Transformation Services implementation. Arguably, the one that has undergone the most significant changes since the SQL 7.0 release is the Transform Data Task. In this article, we will look at its default behavior. We will follow with a discussion of its more advanced features, providing an explanation of why this task is often referred to as the Multiphase Data Pump.
In general, Transform Data Task copies data between source and destination data stores, transforming it during this process. The transformation can range from a simple column copy (which essentially does not perform any changes but simply copies content of a source column to a destination column) through file operations (reading and writing), string manipulations (case change, trimming), to ActiveX scripts (for more information on this subject, refer to our previous articles), which, in extreme cases, can involve custom COM objects.
Transform Data Task requires two existing relational data store connections. For the sake of simplicity, let's use the Northwind database as the source and tempdb as the destination (tempdb is convenient for testing since its content is cleared every time SQL server restarts). Once both connections are available, make sure that the data source is highlighted, and select the Transform Data Task item from the Task menu of the DTS Package Designer interface (or the corresponding icon from the toolbox on the left hand side of the window). When prompted for the destination data store, point to the other connection. To modify the properties of the task, double click on it or select Properties item from its context sensitive menu.
The Properties dialog box is divided into five tabs. The first two, appropriately named Source and Destination, allow you to designate data source and destination. While destination entry must contain a name of a table (this can be an existing or a new table), source data can be derived from a table, a view, or a SQL query. In addition, when using a SQL query, you can include in it parameters (with question marks as placeholders) which map to DTS global variables (for more information on DTS global variables refer to our earlier articles in this series). In our example, choose Shippers table as the source and create a corresponding table in the tempdb database. Transform Data Task will automatically attempt to match source and destination columns and apply Copy Column transform to each pair (this means that there is individual transform for each column in the data source). This is reflected by entries on the Transformations tab - each arrow from source to destination designates a separate transformation, named DTSTransformation__1, DTSTransformation__2, and DTSTransformation__3 respectively (you can see their names appearing in the Name listbox). If you click on Edit, while any of these transformations are selected, you will be presented with the Transformation Options dialog box. On the first tab, labeled General, you can find information concerning the type of transformation (Copy Column) and its sequence (a number indicating in which order this transformation will be executed). You can also change the transformation name by typing a new entry in the Name text box and alter columns mapping by clicking on the Properties command button. List of columns available in the resulting dialog box can be changed by modifying source and destination columns on the remaining two tabs of the Transformation Options dialog box (named appropriately Source Columns and Destination Columns). Once you have finished reviewing all available settings, click on OK to get back to the Transformations tab of the Transform Data Task Properties dialog box.