Database The New Face of DTS in SQL Server "Yukon"

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.

Viewing DTS packages in the Object Browser

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.

An empty DTS 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.

Items for building DTS Data Flows

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.

Setting the source properties

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.

Setting the data flow

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.

The Rest of the Interface

What about the other three areas in the DTS Package Designer? The Control
Flow area gives you a second set of tasks to work with; this set is centered
around moving data around outside of SQL Server. You can set up bulk inserts or
do data mining, move data to XML, use FTP and message queues, as well as set up
basic loops and sequences here. When you create a data flow task it
automatically shows up in the Control Flow area so you can work with it.

The Event Handlers area lets you tie into the actual runtime processing of
your package. Events include OnError, OnPreExecute, OnPostExecute, and so on.
Event handlers are built from items in the Toolbox, just like the packages
themselves.

Finally, the Tree View area, shown in Figure 6, gives you an overview of all
the pieces of the DTS package. You can select any item in the tree to see its
properties in the standard Visual Studio property sheet – though this won’t
necessarily include all of the properties that you can set by double-clicking
the item!

The Tree View shows the contents of the DTS package

Of course, the designer also lets you do other basic package tasks. You can
open an existing task, save a copy of a task, execute it or debug it. As much as
possible, the “Yukon” team has reused the way that Visual Studio .NET works in
designing SQL Server Workbench, moving us one step closer to the day when all of
our tools will have a single unified interface.

The Future of DTS

As the release date for “Yukon” approaches, you’re going to see a lot more
about the power of the rewritten Data Transformation Services. And there’s
certainly a lot of power here; if you’re doing complex data warehousing work,
for example, it’s hard to match this combination of a high-end engine and visual
designer ease of use. But when you’re getting ready to make the leap, you need
to keep the UI changes in mind as well. No matter how much you know about DTS in
SQL Server 2000, you’ve got work ahead of you in learning the new version. I
think it will be worth it, but make sure to set aside the time for the learning
curve.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on
development topics, and the lead developer for Larkware. Check out his MCAD 70-305, MCAD
70-306, and MCAD 70-310 Training Guides from Que Publishing. When he’s not
writing code, Mike putters in the garden on his farm in eastern Washington
state.

Latest Posts

Related Stories