SQL Server 2000 DTS Part 8 – DTS Designer Tasks – Transform Data Task

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.

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles