In-Depth

SQL Server DTS Overhaul Boosts BI Features

Company is enhancing Yukon DTS’s support for data mining, OLAP, and data warehousing.

• By Stephen Swoyer
• 11/19/2003

Even as Microsoft Corp. prepares its first-ever enterprise reporting component, the software giant is also revamping the SQL Server ETL facility—dubbed Data Transformation Services (DTS)—that it first introduced with SQL Server 7.0.

Earlier this month, attendees at Microsoft’s Professional Developers Conference got their first look at “Yukon,” the much-anticipated release of SQL Server. Microsoft has said that the next-generation version of DTS will be built into Yukon, which is expected to ship late next year or sometime in 2005.

That same week, the software giant published a DTS knowledgebase article—called “A Practical Guide to SQL Server Yukon Beta 1 DTS”—to its TechNet portal site. As the new guide makes clear, DTS has undergone a fundamental redesign as part of an effort to make it a more enterprise-grade ETL tool.

Like the integrated Analysis Services that Microsoft shipped with SQL Server 7.0, the first iteration of DTS proved to be enormously popular. As a result, when the software giant shipped SQL Server 2000, it introduced an incremental DTS upgrade as well. “We use Transformation Services for uploading big files into relational data and export out data also,” says one SQL Server administrator. “As far as the usage, I feel it is really fast and does give a decent programming model [to] experiment with.”

Like all first-generation Microsoft products, however, DTS apparently had a few bugs. “DTS has issues and the more I use it the more I tend not to use it,” says one SQL Server administrator who develops SQL Server reporting solutions as a side business. “[I] have had problems with jobs locking up after a few months worth of regular usage.” He concedes, however, that DTS “is fine for quick wizard operations” such as exporting or importing data.

When it’s finished, Microsoft says the revision of DTS will be more usable and manageable and should also require less coding to achieve more complex results.

According to the new DTS guide, many of the design and administration methods implemented in the first version of DTS have been completely redesigned in the Yukon variant, which—among other changes—introduces two new "Workbench" features (“BI Workbench” and “SQL Server Workbench”) that allow developers and SQL DBAs to focus on what they do best.

Nevertheless, existing users of DTS probably have little to worry about: Packages designed for the first version of DTS should run just fine in Yukon DTS, which—in addition to its own native runtime—also includes an implementation of the DTS for SQL Server 2000 runtime. In addition, developers can repackage their legacy DTS packages as Yukon packages and run them with a new task that Microsoft is calling “ExecuteDTS2000 Package.” Finally, the next version of DTS includes a Migration Wizard that’s designed to migrate packages created in DTS for SQL Server 2000 to the Yukon variant. It’s expected that the Migration Wizard will be a less-than-ideal solution for heavily scripted packages, however.

Microsoft says that DTS’ new BI Workbench won’t require developers establish a direct connection to SQL Server as they design packages nor when they’re ready to save their work. Instead, Microsoft says, work can be saved into project folders, after the manner of Visual Studio.NET. Among other features, BI Workbench also features direct integration with Microsoft’s Visual SourceSafe (VSS) tool, such that as developers make changes to projects that are in progress, they can automatically be checked into VSS.

The SQL Server Workbench is designed primarily as a tool for DBAs to manage SQL Analysis and Reporting Services servers. Nevertheless, it does allow developers to design, execute, and schedule DTS packages, but doesn’t include source control features.

Both Workbenches support DTS package debugging through integration with Visual Studio.NET.

Next-gen DTS features additional wizards on top of the “Import/Export Wizard” that shipped with DTS in SQL Server 2000. The first of these, Package Installer Wizard, is designed to guide a developer through the steps of building an install for a DTS project automatically. The second, DTS Configuration Wizard, guides a developer through the steps of creating a “Configuration,” which Microsoft says is similar to Dynamic Properties Tasks in SQL Server 2000. Configurations are apparently intended to configure package variables and properties at run time.

Microsoft will also introduce changes in the Import/Export Wizard, including a revamped user interface.

The integrated development tool—called DTS Designer—that Microsoft shipped with DTS for SQL Server 2000 is also getting an overhaul in Yukon DTS. DTS Designer provides a set of graphical tools designed to facilitate data movement, workflow and complex data transformations with minimal or no programming. In the earlier version of DTS, the Designer windows for Control and Data flow were combined; in the revamped DTS Designer, Control flow and Data flow editors are separate.

Microsoft is enhancing Yukon DTS’ BI and analytic credentials as well, introducing enhanced support for data mining, OLAP, and data warehousing.

As to data mining, the next version of DTS features new data transformations that help to measure the effectiveness of previously built mining models. It will also include a facility to train one or more mining models based upon data provided in an input stream. Finally, it will be able to run one or more prediction-oriented, data mining queries against the data provided in an input stream.

As far as OLAP is concerned, Yukon DTS makes it possible to stream relational data into a dimension within an OLAP database or into an OLAP cube’s partition.

On the data warehousing front, the next version of DTS includes data transformation features that help developers better manage slowly changing dimensions.

Microsoft says it’s also building enhanced data and process reliability features into Yukon DTS. One such feature, a “Checkpoint” recovery capability, is ideal for extremely large jobs, where a single error—even very late into a package—can cause the entire process to fail. The upcoming version of DTS supports logical checkpoints—i.e., recovery points—where the state of a package is persisted. In this regard, Microsoft says, a job can be restarted at the point of failure or at the most reasonable place in the flow prior to the error.

One of the biggest changes in the Yukon version of DTS is the pervasiveness of the .NET Framework. To that end, Microsoft says, Yukon DTS supports managed wrappers and interop assemblies that let developers create custom tasks and transformations in .NET languages such as C# and Visual Basic .NET.

That’s just the tip of the iceberg. Check out the complete Knowledge Base article, “A Practical Guide to SQL Server Yukon Beta 1 DTS,” at http://info.101com.com/default.asp?id=3759