Book review – Extending SSIS with .NET Scripting

Recently I got my hands on a copy of Extending SSIS with .NET Scripting, written by SQL Server MVP Joost van Rossum (blog | twitter). My .NET scripting knowledge could use an update, so I decided to read the whole book and provide you with a full review.

About the author

I’ve known Joost van Rossum for quite some time now and he is definitely an expert in SSIS. He’s very active in the MSDN forums where he also takes on the role of moderator. Joost also has his own blog, where he mainly blogs about SSIS. He has numerous post about scripting in SSIS and he has also written his fair share of custom components. For example, a for each loop where you can use regex expressions. If anyone should write a book about .NET scripting in SSIS, it’s Joost.

Régis Baccaro (blog | twitter) also write a few chapters in the book. He’s a SQL Server MVP and very knowledgeable in a lot of domains, such as BI, SharePoint and C#.

General

First of all, I liked the book, a lot. It covers scripting in the script task and the script component and it comes packed with practical examples. The book is littered with code snippets you can use immediately (they are also provided as a separate download). The book is well written in a very understandable way. A lot of screenshots are used, so it’s hard to miss anything. There are a few typos here and there, but not too much to annoy me. It’s quite a lengthy book, with a healthy 481 pages. However, all of the code samples are written in C# and in VB.NET, so if you’re interested in only one programming language there are a lot of parts you can skip. Personally I would have left out VB.NET (who uses that anymore? (joking)). I understand why they include it and it makes the book very complete, but it also leads to excessive scrolling sometimes. It doesn’t make the book bad, but also – in my opinion – not that much better. But again, I understand why they included the VB.NET snippets (and yes, in SSIS 2005 it was your only option but we’re 2016 now).

You can read the book front to back, but you can also skip to a chapter of your interest. For example, if you want to use web services in a script component, you can go to the relevant chapter and directly start reading/coding. Almost all of the chapters are independent of one another, so you can read them in any order you’d like. This makes the book an excellent reference book. I’d like to keep a digital copy with me every time I go to a client, just in case I might need some .NET snippets.

Chapter by Chapter

Let’s take a look at what’s inside the book.

• Chapter 1, 2 and 3 are your standard “Let’s get started” introduction chapters. Chapter 1 introduces SSIS and you can skip this one altogether if you have worked with SSIS for longer than 10 minutes. I guess this is probably for most of the readers who picked up the book; scripting in SSIS is a more “advanced” topic in my opinion. If you’re an absolute beginner, this chapter is a must-read. Chapter two explains the script task and the script component along with the difference between those two. There are some interesting pieces in there, such as how to build a script (my most popular post on my old blog). Again, if you have ever worked with a script task/component, you’d probably find this chapter too basic. Chapter three talks about C# and VB.NET programming basics. This chapter is about 12 pages long, which means it’s too short for a solid introduction. If you’ve never used written a line in those programming languages, this introduction is probably a bit too basic. But I get it, this is a book about SSIS scripting, not C# for dummies. The chapter is useful though if you need to quickly look up a bit of syntax. All in all, if you have ever written .NET scripts in SSIS before, you can safely skip those first three chapters.
• With chapter 4 the real works begin. It explains how the script tasks work and how you can configure. There is a very excellent and detailed description on how to handle variables and connection managers. There’s also a great part on firing events between parent/child packages. Even if you have already worked with script tasks, there’s bound to be some useful information for you in this chapter.
• Chapter 5, 6, 7 and 8 all provide you with useful real-life examples of script tasks. Chapter 5 talks about handling files and everything you need to know about that. A great example is deleting files older than x amount of days. Chapter 6 deals with the Internet and the Web, such as sending HTML-formatted emails, downloading files and using SFTP (not supported by SSIS itself). Chapter 7 talks about my two most favorite subjects (ahem): XML and web services. It goes into great length on how to use web services. This is one of the more complicated chapters of the book, but the authors do a really good job in trying to present it in a digestible manner. This is one of the chapters that I probably will only read again if I actually need to use the code. Chapter 8  has the more advanced examples (although chapter 7 is more difficult in my opinion, but that might be because of my lack in C# experience). Some of the examples are zipping, encrypting and regex expressions.
• Just like with the script task, there is one chapter introducing the script component, followed by chapters full of examples. Chapter 9 explains the script component. It also does a good job on explaining how to deal with variables and connection managers. Since the script component can be used as a source, a transformation or a destination, there’s a chapter for each of its role. Chapter 10 gives a few examples of using a script component as a source: reading odd text files formats and generating random data. Chapter 11 deals with the transformation (which is how I use the script component the most). The difference between synchronous and asynchronous is treated very well, again by using different examples. Some of the examples are encrypting/decrypting data, comparing rows, text transformations, generating row numbers and the conditional multicast (a classic example of an asynchronous transformation). Chapter 12 has the script component as a destination: writing header and footer to a text file and writing to an XML file. Chapter 13 is a great chapter that really goes into the detail of using regex expressions. Examples are data cleaning/validation and removing HTML tags. Chapter 14 is all about reflection. This is what separates the man from the boys, sort of speak. Reflection makes your scripts more flexible. A great example is calculating a hash over all the columns. The script in this book is superior then the one I wrote a while back. One of the best chapters of the book! Chapter 15 deals once again with web services. It provides a set of examples on how to work with the Dynamics CRM web services. I skipped those since I currently don’t work with Dynamics CRM, but I know where to find the code if I should ever need it. There are also some examples on SharePoint, should you be interested. Pretty advanced stuff, and a great addition to your toolbox!
• Chapter 16 and 17 explain how to write your own custom task/transformations. I glanced over these chapters, since I probably never going to write. However, if you’re interested in doing so, these chapters will make your life easier. Joost has a lot of experience with this subject matter, so check it out if you want to get started in creating your own SSIS tasks.
• Chapter 18 and 19 deal with scripting from .NET applications. Chapter 18 talks about programmatically creating SSIS packages, but mainly it talks about BIML. I love BIML and it’s super awesome, but it doesn’t really have a place in this book. The book is about extending SSIS with .NET scripts, so it’s a bit odd to have BIML in there. Also, one small chapter doesn’t really do justice to the power of BIML. On the other, the more people get aware of BIML, the better. Chapter 19 is about executing SSIS packages from .NET again. Again, it seems a bit out of place but there’s some useful info in there.

Conclusion

This book is certainly a must-read for peopling who want to go beyond the limits of what SSIS has to offer out of the box. It will teach you about everything there is to know about script tasks and script components. There are a lot of real-life examples and the code snippets are definitely a great addition to your toolbox. There are some chapters that you can easily skip and almost all of the code examples are in C# and in VB.NET.  This book is a great reference for scripting with .NET in SSIS.