Thomas Williams

October Melbourne SQL Server SIG - "Indepth Look at DTS in SQL Server 2005"

This review is a bit old, but probably still worth posting to gather my own thoughts on the October SQL SIG.

David Lean (I couldn't find his blog or website, sorry) presented October's SQL Server SIG on Tuesday, October 19th, on the topic of “An Indepth Look at DTS in SQL Server 2005” at Microsoft's offices on Chapel Street, Melbourne.

I use DTS for importing files into my applications (among other things) and was keen to see how Microsoft had improved both the design-time environment and the run-time options. I was particularly interested in debugging & error handling, two areas I found pretty weak in the current incarnation - although I can safely admit that my knowledge is pretty thin, too.

The event was well attended and David kicked off by highlighting the importance of DTS in SQL Server to date and conducted an impromptu poll of how many people in the room had used DTS on that particular day. A lot of hands went up, mine included. On an almost administrative note he mentioned that DTS would be renamed in Yukon to “SQL Server Integration Services”, kind of promoting it alongside Reporting Services and Analysis Services. He took a little bit of time to go over the new Enterprise Manager, which is good for me since I don't have a beta of the Yukon software and have never laid eyes on it.

Then came an overview of the 4 core parts of DTS - the DTS service itself, the DTS run-time (which can use COM objects or managed code and does the work of copying files, creating directories, etc.), the DTS data flow engine (which is the data transform, “ETL” part of DTS) and DTS clients (like wizards and command line tools that use the DTS object model). One cool improvement is the ability to save DTS packages as XML files which can even be encrypted. Moving packages from a dev server to a production server is apparently a lot easier, too, with connections able to be easily edited.

David's demos covered a lot of ground and he was clearly very knowledgeable about this product. When he started getting into the demo side of things, I noticed some of the new options available in the designer toolbox: execute process task, script task, FTP task, WMI event watcher task, file system task (enumerate files or folders, among other things), web service task, database maintenance task, backup database task, defrag index task...and the list went on.

During David's talk it was apparent that the level of control for the developer had got very granular - for instance, data types could be set on columns of flat file imports, and when a violation occurs the row can be ignored, the data can be truncated, or the whole task can fail depending on the business need. Error handling is also a lot more granular, using event handlers or “on error”-tasks, and various log providers are available (this made me happy).

Tasks can be grouped either visually - just for display - or logically/atomically (if one task in the group fails, the entire group fails). David spent time talking about one of my bugbears with the current DTS - conditional splits (i.e. if x condition, then branch to y action/group of actions). In Yukon they are much better handled, with the ability to name specific paths through the workflow and conditionally execute them. David also mentioned that variable scope had been improved (he talked about a bug in DTS 2000 where DTS packages can alter variables in other packages - well, no longer!)

I must have got tired of writing towards the end of the presentation. One of the last things I've noted is David's demonstration of “fuzzy lookups” which were quite cool. Basically threshhold values can be set and matches can be made, even on inexact data.

All in all it was an excellent and information-heavy session. Well done David! I think seeing Yukon's DTS in action got me more excited about SQL Server 2005. I admit that up to this point I had seen it as a “minor upgrade” to the functionality I used, and had not seen too much that I would get immediate value out of. This is considering I don't use high availability, clustering, replication, distributed transactions or full-text indexing. Now I can see ways to get better value out of DTS and particularly leverage my VB.NET skills and the framework over DTS 2000's VBScript.