Scripting SQL Server 2005 Integration Services by Donald Farmer

Scripting SQL Server 2005 Integration Services

  • Author: Donald Farmer
  • Softcover: 192 pages
  • Publisher: Rational Press (Jul 15 2005)
  • Book dimensions: 8.7 x 6.1 x 0.5 inches
  • ISBN: 1932577211
  • ITBookworm score: 4.0 /5

Score Reasoning

I really wanted to give this book 5 stars. It has almost everything you need in a book of this type. It’s a complete tutorial that answers most of your questions right about the time you think to ask them. However, I just think it failed to cross the finish line by not giving more examples and by not showing how to code custom tasks. I really wanted to see some of that and it was barely mentioned at the end. Still, if you’re not that interested in that, then this book will deliver 5 stars for you.


How would you like to learn SSIS scripting from the group product manager for SSIS at Microsoft? Donald Farmer has written this very concise tutorial that will get your SSIS scripting kicked off to the right start. I have spoken to Donald a couple times and he really knows what he’s doing. And once you start reading, you’ll realize that this book is written by one of the best resources on the subject. The buck stops here. This is really an excellent book. It’s not only concise and teaches its subject matter very thoroughly, it also gives tips along the way about working in the SSIS environment. A product like SSIS is very intricate. There are hidden doors everywhere, and while this book isn’t dedicated to this pursuit, these gems are sprinkled throughout. There are some excellent pointers here, and even more, there are some good solid coding examples that will get you on your way to coding in SSIS. Every aspect of coding is discussed from writing simple scripts, to creating your own transformations and assemblies. Writing assemblies isn’t really tutored though, so don’t get your hopes up. Again, this is a book about coding in SSIS and it sticks to the subject matter very well. To learn the basics and even some intermediate scripting skills in SSIS you won’t find a better resource. It’s also a quick read. I finished it on the plane on the way to the Yukon launch in S.F. It will take you longer to commit some of the lessons to memory, but a little practice will get you there in no time. There’s one more thing I really like about the book. If you register it, there are a few extra chapters to download and they’re more than worth the time you’ll spend online. Don’t be fooled by this being a beta preview. The scripting model hasn’t changed enough to matter. While it’s impossible to get away from a subject like this without teaching some .Net code, this is not a tutorial on writing, so don’t expect to walk into this with no knowledge of the language and have everything explained to you. And while it does a very good job of showing the basics, I really would have enjoyed some more examples of each type of script so that I could see the techniques in more than one situation. I would also have liked some exercises at the end of each chapter and maybe some knowledge building review questions or something. Don’t get me wrong, it’s a fabulous book, but I’m one of those people who wants everything in one place. I also wish it actually went into assemblies and custom tasks.

Who is this book really for?

Let me start out by saying who this book isn’t for. This book isn’t for those who don’t know anything about .Net. This book isn’t for those who know nothing about DTS or SSIS. In my opinion, it’s also not for those who aren’t used to the issues associated with solving complex business problems using DTS and scripting. This book is however for those who have experience with DTS and even some light experience with SSIS. It goes over in pretty good detail the explanations of the code and why things need to be done as they are, but it doesn’t go out of its way to teach .Net or other DTS or SSIS concepts that should already be familiar to the reader. If you have experience with DTS or SSIS (beyond the wizard) already, and you want to know how to port your scripting skills to the new platform, then you’re in the right place.

Writing Style

You know, it’s really not a bad style at all. It was more interesting than BOL, but not quite as interesting as… yeah, pretty much anything with Jennifer Aniston. Seriously though, I don’t recall him going out of his way to be funny or anything, but you definitely won’t be bored. Now, that may be because I was so interested in the material, or because the book is over before you know it. All the same, for a relatively complex subject, it’s a fairly light read.


I bent the spine backwards on the plane and read for quite a while. I also worked the spine a bit trying to see if it would tear. I then set it under a stack of books with the spine bent back to put it under a good deal of stress for about a week. It held up fairly well, though I did start to see some separation towards the middle of the book. It hasn’t actually separated, but it looks like it really wants to. So, overall, I’d say it’s made fairly well, and as long as you’re not as rough on it as I am you’ll be just fine, and it should stand up to being passed around to the whole department, or just stuffed in your bag and carried around as a reference like mine.

Table of Contents

Chapter 1: An Overview of SQL Server Integration Services

This is a pretty small chapter. It just discusses the basic components of SSIS. No detail at all, just a quick overview of the major pieces. If you don’t know anything at all about SSIS, well, first of all you don’t need to be reading this book, but just in case you don’t know anything about it, then this is a very high-level intro for you.

Chapter 2: SQL Server Integration Services Architecture

Here the basic elements of an SSIS package are discussed. Data flows, tasks, constraints, etc are all given a little time. Then it goes into the designer and the basic elements contained within. SSIS clients and services are discussed next and it’s actually interesting what you find out here. I know I had misconceptions about what the service’s role is, and this chapter cleared it up for me. Now you get into a little more about data flow and the different types of transformations you can use. It gives a very basic overview of each component, but this isn’t meant to be an all-encompassing tutorial on SSIS so it’s plenty for the subject at hand. A good chapter if you are new to SSIS, but you can skip it if you have even a little experience with it.

Chapter 3: SQL Server Integration Services Scripting

This chapter is just a couple pages on why you need to use script to begin with, and the language choices available to you.

Chapter 4: Your First Script Task

This is where you actually script for the first time. This chapter does a good job at walking you through your first script by showing you step by step how to create a script in a package. It shows screenshots on the editor and the different screens and explains what most of the options mean. It then goes into the default script that comes up when you open the editing window and explains each line of the code so you know exactly what’s going on. Now you finally get to write your first script. We should all take this opportunity to thank Donald for not making us endure Hello World again too.

Chapter 5: Integrating the Script Task with Your Package

This starts out right away talking about using variables with the script task and again, gives a pretty thorough walkthrough with screenshots. Adding references to the different types of variables, and working with them inside your script are gone over in some detail with example code to guide your way. I love how this book doesn’t waste any time. Now you jump straight into debugging data flows and scripts and how to use the basic debugging features in SSIS.

Chapter 6: Advanced Uses of the Script Task

Again, getting right down to the point with logging activity in your packages is the first focus here. It’s really a very thorough explanation of the configuration and management of logging. Of course, it wouldn’t be complete with out a walkthrough on adding events to your scripts. SSIS will log many of these events automatically, but here you can code your own messages at any step you feel. Using .Net assemblies is discussed in complete detail. One of the things I like is that this book is an actual tutorial so you get plenty of step by step instructions, and even the simplest ones like creating variables are repeated in every chapter so you don’t have to go back and look them up. This chapter really does supply you with a very clear example of how to use assemblies in your package, and call them in your scripts. It then teaches you in step by step detail how to access assemblies that don’t come with SSIS. These may be assemblies that you’ve written yourself or bought from a vendor. I personally hadn’t realized how easy it was to import an assembly into a package.

Chapter 7: Your First Script Component

You’re finally starting to get into some actual nuts and bolts of SSIS scripts. Some specifics that you’ll need to know about the different types of components are discussed. It starts out talking about the source component, what it does, and the types of inputs and outputs you can expect. It then moves into the same discussion about transformations and destinations. You then get to walkthrough creating a transformation task to a package, and again, it’s got full screenshots along the way. Then you go through adding columns and other elements to the script. Adding code and debugging are next and it’s a very thorough walkthrough that’s done tutorial style so you will actually learn how to create a script component using this chapter. It offers some good advice on debugging your script using the row count component.

Chapter 8: Asynchronous Script Components

This one starts out discussing the difference between synchronous and asynchronous components. Then you get into writing your own asynchronous component. Like everything else in this book it’s a very thorough tutorial, so you know exactly what’s going on and can not only follow along, but you have the knowledge at the end of how to go out and create your own components. Then you go into debugging the component, and then create another one from a step by step walkthrough. Good stuff.

Chapter 9: Script Sources and Destinations

The problem discussed here is how to process files that have formats that can’t be easily parsed by normal means. The solution is to create a custom source component and add the proper outputs. Then use the script to parse the file and send the results to the output. This is what you learn to do in this chapter. The tutorial is quite thorough like all the others and works really well. You then go through the same exercise with a destination component. You actually get to kill two birds with the same stone by not only getting to learn how to create a destination component, but you also get to write to an XML file, and that’s a handy skill.

Chapter 10: Useful Examples

I’m not going to go into everything this chapter covers. Let’s just say that there are some useful examples on things you can do in scripts. It’s really very handy for continuing to build your skill set.

Chapter 11: Moving Beyond Script

Just a very very short chapter discussing where your new found scripting can take you. Things like assemblies and custom tasks are possible and quite accessible. However, it doesn’t provide any examples at all, it only discusses that they’re possible. The chapter’s only a couple pages long.

-Sean McCown,


Leave a Reply

Your email address will not be published. Required fields are marked *