Professional SQL Server 2000 DTS

Highly Recommended!

I was really looking forward to this book as a potential "bible" for DTS. Ever

since DTS has been released, information has been sparse and most books that

I have seen provide the same few types of examples, none of which have really

helped me to learn DTS.

Until this book was published, the best information I had seen about DTS was in

OLAP Unleased which contained a few hundred pages of information. Unfortunately,

most of this information appeared to be aimed towards VB programmers

implementing DTS into VB. There is nothing wrong with this, but as a

DBA, I try to stick to the native SQL Server tools as much as possible. This

way, I have fewer dependencies and do not have to install anything on my

machine or server.

So, with much enthusiasm and some high hopes, I dove into Professional SQL Server

2000 DTS.

The first four chapters present a nice overview of DTS and walks the user

through a few basic and fairly standard examples. The tasks that are included

with SQL Server 2000 are each presented with a good exmaple of each option

and setting. I think the explanations are fairly straightforward and written for

most imtermediate to senior level DBAs. I especially like the way that the

various constants that are available are defined along with a few examples of

how to use each. If you have done Windows programming, then these constants

will look familiar, though none of the DTS books I have looked at before

this one have presented very good explanations of each in this much detail.

Chapters 5 and 6 discuss accessing heterogeneous data access, which is probably the main purpose behind DTS. Chapter 5 looks at linked servers and how they are setup in SQL Server. It is nice to have this chapter separate so it can be skipped by those who know how to do it, but still there to build a base for those who have never setup a linked server. Chapter 6 then dives into various examples of how to access data. There is an Excel example that includes formatted titles and headers in the spreadsheet (how many Excel spreadsheets do you get without a title or header in the first couple rows?). That alone is something that lots of people that have not used DTS very much would get frustrated trying to get working. And it's a simple fix (set first row to the real first row on the options tab)!!

There is also a nice example converting data from DB2 using the Host Integration Server from Microsoft. One nice thing in this example is the inclusion of specific errors (using screen shots!!!) that may occur when using this tool. I do not have a copy of either DB2 or Host Integration Server, so I could not test this. The only thing that would round out this chapter would be an example of data transfer from Oracle. Since it is so heavily used, it would be nice to see a specific example that queries an Oracle database.

Chapter 7 is the chapter that I was most interested in reading. ActiveX scripting is something that I have hacked around with, but have not really had the time to develop good skills here. No other book I have read on SQL Server walks the beginner through ActiveX scripting. This chapter presented a nice introduction to programming using scripting and the various types of programming constructs that are available. I am familiar with most of the constructs, but the seeing the specific implementation of loops, variables, etc. in scripting was nice (with screen shots).

Chapter 8 deals with dynamic packages. Since most packages that I have built with SQL Server 7 have to be tested by me, moved to another server for QA, and then deployed to a production server, I am constantly developing tricks to allow the same package to be used in all three environments. Usually I have had to alter the package as it moves between each environment to reset some of the connections or tasks as databases and servers change. This has been a constant hassle, not to mention a concern for our QA people.

This chapter was helpful because it explained how to add dynamic steps and packages from within DTS. Without VB! Of course, the chapter does then walk the user through setting up a new VB project and creating a package, including saving this package to SQL Server. For someone whose VB skills are a little rusty, this was a nice read.

Chapter 9 goes into advanced ActiveX scripting, including some file system manipulation. I have spent quite a bit of time learning how to manipulate the file system from with DTS, so this was not much help, but it was a straightforward explanation.

Chapter 10 introduces security. It also talks about backing up and restoring databases, including using the maintenance plans included with SQL Server. I am not sure why this much detail is devoted to an admin task and I think this is the weakest chapter in this book. The explanations are a little skimpy and I think this chapter is a bit misnamed.

Chapter 11 is devoted to error handling. Most people will probably skip this chapter, but it is important to building solid DTS applications. I recommend reading this chapter, which contains a good explanation and examples for handling the errors that occur in different tasks. There are both DTS native and VB examples.

Chapter 12 discusses custom tasks and then walks the user through building one in VB. This was a great explanation of building a custom task. I have not built any for my own use (outside of this example), but I look forward to trying some of the techniques in this chapter.

The final three chapters are devoted to data warehousing and integration with other applications. The treatment of the integration with other applications is a little light, but there are a few examples of how to build a custom package, make a DLL using VB and then call this from other applications.

The data warehousing sections, though, provide a good introduction to data warehousing and how to build a DTS solution for loading a warehouse. There is a detailed example that reminds me of some other WROX programming books where a good sizes project is presented as the final chapter.

The nicest thing about this book is that there are lots of screen shots. And by lots, I mean LOTS. More than most any book I have seen. It really helps with such a GUI intensive application such as DTS to have lots of screen shots that guide the reader through the examples. This book as pretty much lived up to the high expectations that I had and I highly recommend this for all SQL Server DBAs.

Steve Jones

January 2001

This page will contain reviews of technical books, though if you are

interested in seeing what else I read, I have been tracking the books I read on my web site,dkRanch.net.