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.