Improved Tasks
SQL Server 7.0 DTS has only one functional phase, Row Transform, when it transforms data. In SQL Server 2000, Microsoft revamped the DTS Data Pump task, so transformations can have as many as six unique phases. By using the multiphase data pump, which is a component of the Data Pump task, you can add error checking to your package or load data around constraints. You can also monitor the number of successful transformations and restart the transformation if an error occurs. The DTS Designer's graphical interface to the multiphase data pump is disabled by default for usability purposes. After you enable the feature, your Data Pump task might look slightly more complex. To make this option available, right-click Data Transformation Services and select Properties. In the Properties screen, select Show multi-phase pump in DTS designer.
Figure 2 compares SQL Server 7.0's transformation phase with SQL Server 2000's multiple phases. The phases in the multiphase data pump are
- Pre Source—DTS executes functions in this phase before it fetches the first record from the source. You can use functions in this phase to create objects that you use in later phases. For example, you can use this phase to create an ADO object that you'll use to write auditing information to a table.
- Row Transform—This phase, which is the only phase in SQL Server 7.0, transforms the data.
- Post Row Transform—For each row that DTS transforms, it executes the Post Row Transform phase. This phase has two subphases: Insert Success and Insert Failure.
- On Batch Complete—By default, each data pump has only one batch. You can change the batch size in the Transform Data task's Options tab. This phase is the perfect place for reporting the status of a large data load.
- Post Source Data—Unlike the On Pump Complete phase, the Post Source Data phase can access data. For example, you can use this phase to write footer rows to the data you created in the Pre Source phase.
- On Pump Complete—After DTS has transformed all rows, the On Pump Complete functions execute. This phase can't access the data, but it's ideal for freeing up memory that was used during previous phases.
The Data Pump task's Transformation Editor is much easier to use and more functional in SQL Server 2000. SQL Server 2000's DTS has built-in COM components to perform some of the common DTS transformations. For example, one new component can transform the data to uppercase or trim any trailing spaces. Also in the Data Pump task, you can output any failed transformations to exception files. You can configure exception files on the Data Pump task's Options tab.
You can also read and write to global variables easily from several tasks in SQL Server 2000. For example, with the Execute SQL task, you can dynamically load the orders for a particular client ID for any given date by using a custom stored procedure and a question mark to represent the variables:
EXEC INS_LoadOrders @ClientID = ?, @DateLoad = ?
In addition, you can output the results of an Execute SQL task into a global variable. You can store either a specific record or the entire rowset in a global variable for later use. You can also use similar input variables in Data Pump tasks. For example, you can use the Data Pump task to select from the source all records that meet certain criteria, such as records that contain a client ID.
Tool and Engine Improvements
SQL Server 2000 adds several benefits to the DTS tools that help you develop and debug packages. These benefits include the ability to cache packages when you open them in DTS Designer, enhanced logging, and the ability to save the packages as Visual Basic (VB) files. In SQL Server 2000, DTS can cache your packages in memory at design time if you're running Win2K. DTS in SQL Server 7.0 is extremely slow at opening packages of any significant size (generally more than 1MB). This slowdown happens because when you open the package, DTS must search your registry to determine whether any new OLE DB provider, task, or scripting language has been added. After you turn on caching, your packages open much more quickly in DTS Designer because DTS skips the registry search step in Win2K.
You can turn on caching by right-clicking Data Transformation Services in Enterprise Manager, selecting Properties, then clicking Turn On Cache. After adding a new custom task, provider, or scripting language, you need to refresh your cache so that DTS can scan it.
On this same Properties screen, you can turn on just-in-time debugging, which lets you use debugging tools to step through your ActiveX Script task if you have any errors or if a forced stop occurs. This option uses the Microsoft Script Debugger, which ships with NT 4.0 Option Pack, Visual InterDev 6.0, and Win2K. You can also download the Script Debugger at http://msdn.microsoft.com/scripting. Keep in mind that the options you select on the Properties screen are in effect only for the client that is executing or designing the package.
As you select connection options, DTS usually verifies each connection and database to make sure the database exists. Disconnected Edit is a new DTS Designer mode that lets you design a package while you're working disconnected from your network. With this mode, DTS won't verify each option. (Because DTS performs the verification for your protection, I recommend that only advanced users use this mode.) You can access this mode under the Package menu in DTS Designer. As Figure 3 shows, Disconnected Edit lets you access any DTS property, including such identifying properties as task names.
In SQL Server 2000 DTS, Microsoft has greatly enhanced logging capabilities. Whereas SQL Server 7.0 can log only at the package level, with SQL Server 2000, you can log the success or failure of an individual step or package. You can turn on logging inside each package on the Package Properties screen's Logging tab. You can log packages to the local SQL Server instance or consolidate the logs to a central SQL Server instance. You can also write logs to a file. This new feature is useful when you're executing a package from within a package because it accurately logs the events that occur in the child packages.
As you debug a package, you'll often need to execute individual steps in the package. In SQL Server 2000 DTS, you can do this by right-clicking any step and selecting Execute Step. In SQL Server 7.0, you can't execute an individual step through DTS Designer. Instead, you have to disable every step except the one you want to execute, then run the entire package.
The fastest way to learn the DTS object model is by saving a package as a VB file. For SQL Server 7.0, a tool was available on Microsoft's Web site to convert a package to a VB file. Microsoft has incorporated this functionality into DTS in SQL Server 2000 as an additional way to save your packages in DTS Designer. After you save the code in a VB file, you can copy and paste it into your VB program or view it in almost any viewer (e.g., Notepad). After you save a package in this format, however, reverse-engineering it into a graphical format is difficult. If you want to save a package in a VB file, always keep a copy of the package in another format for editing.
SQL Server 7.0 users can use the command-line utility Dtsrun for executing packages. However, the commands can be lengthy and the switches complex. In SQL Server 2000, Microsoft added a new tool called dtsrunui.exe, which installs by default in the \Program Files\Microsoft SQL Server\80\Tools\Binn directory. You can execute the tool either from a command line or from Windows Explorer. Dtsrunui.exe lets you quickly generate commands, encrypt commands, or simply execute a package through the GUI.
Where Do You Go from Here?
Microsoft has revamped DTS in SQL Server 2000 to give you added performance, usability, and customization abilities. Do you have a problem that you can't solve through one of the built-in DTS tasks? Why not build your own? In SQL Server 2000, as in SQL Server 7.0, Microsoft implemented all the built-in DTS tasks as custom tasks, so you can call and remove each built-in task just as you would a task that you create from scratch.