The New Face of DTS in SQL Server "Yukon"

Data Transformation Services (DTS) is the piece of Microsoft SQL Server that
lets you automate the processes of extracting data, transferring it from one
place to another, and loading it (sometimes called ETL functionality). It's long
been a useful tool for the dba who has to work with a variety of data sources,
allowing easy automation for quite complex processes.

SQL Server "Yukon" includes some serious changes and upgrades to DTS. It's
still recognizably the same tool, but it has improved capabilities and a
completely revamped design interface. In this article I'll show you some of
what's new in DTS, and display a bit of the new interface. One caution, though:
I'm working from the PDC preview release of "Yukon", which is certainly not the
final version. It wouldn't surprise me at all to see some of these features and
user interface innovations vanish before the product actually ships. But it does
seem certain that we're in for an exciting upgrade even if it's still being
tweaked.

The Bullet Points

Let's start with a quick rundown of what's new in DTS in SQL Server "Yukon".
Some of these changes are large, some less so, but they all represent new things
for the dba to learn about and try:

Separation of data flow and control flow into two different engines.

A new extensible object model that lets you build your own custom tasks
using any .NET language.

A new DTS designer, which can be hosted by either SQL Workbench or Business
Intelligence Workbench.

New transformations that extend the data-processing abilities of DTS

Easy editing of package properties

A DTS installer for deploying finished packages

Most of these changes will only be of interest to the developer who spends a
lot of time working with DTS. I think it's unlikely, for example, that most of
us will ever build custom DTS tasks. Nor do I think deploying DTS packages will
be important for many dbas; if you can develop your packages on the server where
they'll be used, deployment isn't really an issue for you. But the new DTS
designer has an impact on every user. It's also the area where the changes are
most striking. So for the rest of this article, I'll show you what the process
of building a simple DTS package looks like in "Yukon".

The Test Package

To demonstrate the new DTS interface, I'm going to build a package that
copies some data from the authors table on one server, transforms part of the
data to upper-case, and then creates a table on a second server. Along the way
you'll see most of the basics of building a package.

The first thing to understand is that you have your choice of two separate
environments for designing a DTS package: SQL Workbench and Business
Intelligence Workbench. SQL Server Workbench works in immediate mode, which
means that you need to be directly connected to a server. Business Intelligence
Workbench lets you work in disconnected mode. Business Intelligence Workbench
also includes some additional capabilities, including the package and deployment
wizard for DTS. Business Intelligence Workbench can also group your DTS projects
together with other analysis-oriented projects such as reports. For this first
exercise, though, I'll use SQL Server Workbench, which (like Business
Intelligence Workbench), runs in the Visual Studio .NET shell.

So, to start building the package, I launched SQL Server Workbench and
connected it to my test "Yukon" server. This makes a number of tools available,
including the Object Explorer (shown in Figure 1). As you can see, Object
Explorer provides a treeview of a number of parts of your SQL Server
installation, including DTS packages. To create a new package, right-click on
the Packages node and select Add New Package.

Building the Data Flow

Figure 2 shows the new DTS package in the designer. I've selected the Data
Flow area of the designer to start; the designer provides multiple views of the
package.

The Data Flow area lets me build a task that moves data around. To start,
I'll right-click in the Connections tab and select Add New OleDb Connection.
This lets me specify a connection using the familiar Data Link Properties dialog
box, including server, logon information, and the database that I want to use.
For this example, I've created two connections, one each for the source server
and the destination server.

The next step is to add the necessary items to the task. This is done by
dragging and dropping icons from a tab in the SQL Server Workbench toolbox. As
you can see in Figure 3, there are a great many items available in Yukon's DTS.
For this task, I'll need an OLE DB Source, a Character Map, and an OLE DB
Destination.

The next step is to refine each of the items that I've added. To start, I can
double-click on the OLE DB Source to open the Source Properties dialog box,
shown in Figure 4. This dialog box lets me associate the OLE DB Source item with
one of the data sources that I created, and to further specify exactly which
data should be returned by the source.

The other thing that I need to do is wire up the various items together. As
Figure 5 shows, when you select an item in the Data Flow area, it displays a
green arrow and a red arrow. Either of these can be dragged and dropped to
another item. Green, of course, is for the success path and red is for the
failure path.

Of course, I need to set the properties for the other items as well. The
Character Map item lets me select fields to transform, and decide whether to
make the upper case, lower case, and so on. For the OLE DB Destination item, I
can select the target table, and map columns from the input source or other
items in the pipeline (such as the Character Map columns) to output columns.