Foreword

It was back in 2001 when I first started to manage the then data transformation services team. At that time, I'd just moved over from working on the Analysis Services team. I did not have much of a background in DTS but was a great fan of the product and was willing to learn and eager to get started. The question was, What is the best way to get up to speed with the product in a short amount of time?

As I asked around, almost all my new teammates recommended "the red book," which of course was Brian Knight and Mark Chaffin's Professional DTS book. And right they were; this book is comprehensive, detailed, and easy to follow with clear examples. I think that it has been invaluable to anyone who wanted to get started with DTS.

Since then a few years have passed, and DTS has evolved into SQL Server Integration Services (SSIS). The philosophical foundations and the customer-centric focus of both these products are the same; their origins undeniably are the same. But SSIS is a totally different product that plays in a very different space than DTS. Indeed DTS is a very popular functionality of SQL Server. It is used by almost everyone who has a need to move data or tables in any from. In fact, according to some surveys, more than 70 percent of all SQL Server users use DTS. Given the popularity of DTS, one might ask why we chose to pretty much rewrite this product and build SSIS.

The answer lies in what most defines the SSIS/DTS team: listening to our customers. We had been hearing again and again from customers that while they loved DTS, they still felt the need to buy a complementary ETL product, especially in the higher-end/enterprise space. We heard a repeating theme around performance, scalability, complexity, and extensibility. Customers just wanted more from DTS. Among those providing us this feedback were the authors of this book, and I personally have had a lot of feedback from Mark Chaffin on the evolution of DTS into SSIS. Along with the need to greatly expand the functionality, performance, and scalability of the product, there was the implicit need to adapt to the emerging .NET and managed code architectures that were beginning to sweep our industry. All this together led to the only logical conclusion, and this was to build a new product from the ground up, not just to tweak DTS or even to build on the legacy architecture. After we shipped SQL 2000, this effort to take DTS to the next level slowly began.

Luckily for us, we had some great vision and direction on what this new product should be. Euan Garden, who had been the program manager for DTS, Gert Drapers, who was then architect/manager for DTS, Jag Bhalla, whose company we had acquired, and Bill Baker, the general manager for all of SQL Server's Business Intelligence efforts, provided that initial direction and set the course for what was to become SSIS. The DTS team was still part of the Management Tools team, and it was only in 2001 that it became a separate team. It was still a very small team, but one with a clear and very important mission: complete the SQL BI "stack" by developing an industry-leading ETL/data integration platform.

So here I was in the summer of 2001, taking over the team with a huge mission and just one thing to do: deliver on this mission! The initial team was quite small but extremely talented. They included Mark Blaszczak, the most prolific developer I have ever met; Jag Bhalla, a business-savvy data warehouse industry veteran; James Howey, a deeply technical PM with an intuitive grasp of the data pipeline; Kirk Haselden, a natural leader and highly structured developer; and Ted Lee, a veteran developer of two previous versions of SQL Server (just about the only one who really understood the legacy DTS code base!). We built the team up both via external hiring and internal "poaching" and soon had most of our positions filled. Notable additions to the team included Donald Farmer, the incredibly talented and customer-facing GPM who now is in many ways most identified with SSIS; Ashvini Sharma, the UI dev lead with a never-say-die attitude and incredible customer empathy; and Jeff Bernhard, the dev manager whose pet projects caused much angst but significantly enhanced the functionality of the product. Before we knew it, Beta 1 was upon us. After Beta 1 we were well on our way to deliver what is now SSIS. Somewhere along the way, it became clear that the product we were building was no longer DTS; it was a lot more in every way possible. After much internal debate, we decided to rename the product. But what to call it? There were all sorts of names suggested (e.g., METL) and we went through all kinds of touchy-feely interviews about the emotional responses evoked by candidate names. In the end, we settled on a simple yet comprehensive name that had been suggested very early on in the whole naming process: Integration Services (with the SQL Server prefix to clarify that this was about SQL Server data).

That DTS was part of the larger SQL BI group helped immensely, and the design of SSIS reflects this pedigree on many levels. My earliest involvement with DTS was during the initial planning for Yukon (SQL 2005) when I was part of a small sub-team involved in mocking up the user experience for the evolution of the DTS designer. The incredible potential of enabling deep integration with the OLAP and Data Mining technologies fascinated me right from the beginning (and this fascination of going "beyond ETL" still continues — check out www.beyondetl.com). Some of this integration is covered in Chapter 6 of this book along with Chapter 4, which provides a very good introduction to the new Data Flow task and its components. Another related key part of SSIS is its extensibility, both in terms of scripting as well as building custom components (tasks and transforms). Chapter 14 of this book, written by Darren and Allen (who also run SQLIS.com and who are our MVPs), is a great introduction to this.

I should add that while I have written this foreword in the first person and tried to provide some insight into the development of SSIS, my role on the team is a supporting one at best, and the product is the result of an absolutely incredible team: hardworking, dedicated, customer-focused, and unassuming. In fact, many of them (Runying Mao, James Howey, Ashvini Sharma, Bob Bojanic, Ted Lee, and Grant Dickinson) helped review this book for technical accuracy. In the middle of a very hectic time (trying to wrap up five years' worth of development takes a lot), they found time to review this book!

I am assuming that by the time you read this book, we will have signed off on the final bits for SQL 2005. It's been a long but rewarding journey, delivering what I think is a great product with some great features. SSIS is a key addition to SQL Server 2005, and this book will help you to become proficient with it. SSIS is easy to get started with, but it is a very deep and rich product with subtle complexities. This book will make it possible for you to unlock the vast value that is provided by SSIS. I sincerely hope you enjoy both this book and working with SQL Server 2005 Integration Services.

Kamal Hathi
Product Unit Manager
SQL Server Integration Services