I have been using SSIS for long time, and have used it on a lot of projects, so I consider myself to be pretty knowledgeable about SSIS. However up to this point I have just learned SSIS when I needed to accomplish something for a project, I have never taken the time to just sit down and study SSIS. Since I do end up using it a lot, I decided to start studying it, and got the Microsoft SQL Server 2008 Integration Services: Problem, Design, Solution book. So far I have only finished chapter two of the book, and already I have found it to be tremendously helpful.
In chapter two, this book tackles the issue of having multiple SSIS packages and handling logging, error handling, and configurations between the different environments. As a solution to this problem the book proposes basing all of your SSIS packages off of a SSIS template that you create. This SSIS template gets a SQL Server connection from an environment variable on the computer that it is running on. The SSIS package uses this connection string to determine what database to connect to in order to get the other configuration information, and to log events to. The second level of configuration is pulled from this database, and is the configuration information that is shared across all packages. This will contain information like the connections to the databases in your environment. When the developers create SSIS packages from this template then they can add a third level of configuration that is also retrieved from the same database and contains the configuration information that is specific to their SSIS package.
The template also has the logging configured. There are events that are configured to log information to the same database that contains the configuration information. It logs the execution start of a package, when the package finishes, and information about any errors that occur.
There is an entire chapter in the book devoted to this subject, so there is no way that I am going to be able to do it justice here, but hopefully I have at least described it enough to peak your interest. I am expecting this to vastly improve how we handle SSIS packages at the company that I work at.
Of course, as a C# developer, this chapter instantly made me think of objects and base classes. Wouldn’t it be great if SSIS packages could truly inherit from a base package. Then if you ever want to update the template you could just update it in one place and all the rest of your packages would automatically be updated with the changes.