Using SSIS in a Team Setting
SSIS or SQL Server Integration Services, can be thought of as a graphical programming language specifically designing for moving and processing data. It replaces DTS packages as one of the primary ways to move large amounts of data into and out of SQL Server from other data sources such as flat files and non-Microsoft databases.
What follows is a summary of the Jamie Tomson's experience using SSIS in a team setting.
The first problem with SSIS packages is that they are versioned as a unit. Unlike a normal code file, you cannot have two people modify the package at the same time and expect to be able to merge your changes together. The underlying XML format simply isn't designed to be human readable. The impact of this can be lessened by using exclusive locks when you check out a package. Another mitigating factor is the ability to call packages from other packages via the Execute Package Task.
Jamie Tomson highly recommends using package templates from the very beginning.
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.
Annotations and naming conventions are also very important to document exactly what a package is trying to accomplish.
In regards to projects and solutions, Jamie suggests:
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.
Ian Culling, Andy Powell & Lee Cunningham Dec 11, 2013