Recently on this blog I mentioned in passing that I have been working for some considerable time now on a project with multiple SSIS developers. A few people asked me if I could give a rundown of how we went about this, any experiences of team development I could pass on, and any pitfalls that I had learnt about. That's what this blog post is all about.
Scene setting first. In August 2006 I started on a project where I headed up a team of 3, occasionally 4, SSIS developers. Our go-live date passed a couple of weeks ago without too many hitches and in the intervening 11 months we have built a system that extracts data from 5 (3 Oracle, 2 SQL Server) systems of record (SoRs) and pushes it all into the most ridiculously complicated data model known to mankind (not my choice - trust me). Our system employs 36 SSIS packages collectively executed nightly by two SQL Server Agent jobs. We employ Team Foundation Server (TFS) as our work tracking and source control system and have a continuous integration build process that bundles up all our SSIS packages, SSIS configuration files, environment variables and folder structure into a single, deployable, parameterizable Microsoft Installer (MSI) file that we can execute against any of our environments (of which we have 4 - development, system testing, user acceptance testing, production). Our system pushes approximately 1million rows of data a night and due to a backloading effort that we undertook prior to our go-live date we now have 29million fact data rows in our data cache. Not massive volumes but not insignificant.
OK, so that gives you an idea of what I have been working on and what my team has achieved. The point of this blog entry is to share learnings on how we achieved it, for better or worse. Off we go.
- Use a source control system. Hopefully that's a given.
- SSIS packages are effectively binary files - you can't really look at the XML code in a .dtsx file and understand what the package does. A downside of this is that merging changes made by two developers is not possible using a merge tool (the like of which appears in TFS). Hence, I try to impress upon my team the need to download the latest copy of all the packages on a regular basis (I suggest every morning) so that they are always working on the latest copy of a package.
- Following on from the earlier points about (a) the inability for two developers to work on the same package and (b) the inability to merge changes, I recommend to my team that when they check-out a package from source control they take an exclusive lock on it. As you can see from the screenshot below this isn't the default in TFS.
- As you have just read we have built a lot of packages. This was deliberate. A SSIS package can only be worked on by one developer at a time - this isn't like developing object oriented code where a class can be partial and thus worked on by multiple developers concurrently. This presents problems if you have a lot of functionality that you want to cram into your package so instead we made a decision to build many smaller packages with limited functionality in each. In essence this all means that we have a system that is greater than the sum of its parts and naturally implies that we make heavy use of the Execute Package Task. A small number of monolithic packages is not the way to go.
- If you only take one piece of advice from this blog entry make sure its this - use templates; they are vital in a multi-developer environment. Templates are the only built-in mechanism for enabling common approaches to those mundane but necessary aspects of SSIS development like logging, annotating, checkpointing and configuration. Spend time at the beginning of your project deciding how you are going to accomplish logging, checkpointing and configuration and build an appropriate template to support those decisions. I have provided an example template here. Don't leave home without one.
- If you need to store passwords set package property ProtectionLevel='DontSaveSensitive' and do so in your template. This ensures you don't run into entanglements with developers not being able to open or use packages built by someone else.
- When you are building many packages it is easy to forget what each one does and the context in which it is used so use a sensible naming convention to clearly differentiate your packages. I have suggested one here but you can probably come up with your own.
- Maintain a list of development practices that all developers should follow. Write a checklist that a developer should run through prior to each check-in and make sure they pin it up somewhere near their monitor. On my project we have a project wiki and I like to maintain these development practices and checklist on there.
- Insist that packages are annotated properly - make them self-documenting. Developers can place annotations onto the design surface - use them. In fact, put skeleton annotations into your template contianing instructions on what the developer(s) should write. Each object within a SSIS package has a 'Description' property. Fill those in as well.
- Use a work item tracking tool so that you know which developer made which changes. As I have alluded to a number of times in this post I highly recommend buying a copy of TFS.
- Use separate projects to group together packages that work in concert with each other or collectively achieve a single goal. Don't be afraid to introduce more projects but only do so where it makes sense to. If you can, use a single solution for all your SSIS packages.
If I think of any more then I shall add them up here later. In the meantime, feel free to pass on your own experiences in the comments section below.
-Jamie