The New Face of DTS in SQL Server "Yukon"

by Mike Gunderloy

Preview Microsoft's plans for Yukon's Data Transformation Services (DTS).

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.

This article was originally published on Monday Feb 9th 2004
Mobile Site | Full Site