My previous post covered how to create and use a SQL Server Integration Services (SSIS) template. Now you might be wondering why would I use these templates and what are the dangers of using them?

The Rain (cons): SSIS templates need to be downloaded to every local machine that you will be developing on and, as addressed in my previous post, it must be stored in the assigned template folder that was chosen in your SSIS install.  I recommend saving the original template on a shared network drive to allow for easy access. Also, when you are using your template for a new package you must generate a new GUID, otherwise SQL Server will be unable to differentiate between your new package and other packages with this same GUID.

In my opinion the most dangerous aspect of using SSIS templates is the potential for losing consistency. It is inevitable that your template will require alterations before the completion of a project. In this event you would of course save the altered template on the share drive and instruct your team members to download the new template and update their local copies. Some team members may fail to do this (maybe they are on vacation, or the timing is inconvenient) and continue developing on the old template. As the project manager your perception is that all packages are consistent, but as the project comes together some missing pieces may come to light.

This scenario results in inefficiency due to extra work and package validation – and of course frustration from all angles.

The Rainbow (Pros): Standardization is extremely important in IT projects because (as we all know) the project will continue to grow – Instead of “scope creep” should we say “scope sprint”? Consistency of design helps keep the project extensible and easier to maintain. In addition, if a key team member gets hit by a bus, standardized designs will be easier to document and communicate when other team members need to be trained.

Other than standardization, SSIS templates are highly efficient. Once the initial template is created your developers will be able to start with a package that already has standardized logging mechanisms, control flow tools, and indirect package configurations. As a developer of SSIS packages I’m sure it is clear how much time would be saved if you didn’t have to set these components up with each new package you create.

The Pot of Gold (overcoming the cons): You have the option of taking a precautionary step using a Team Foundation Server (TFS). Your lead developer can utilize TFS source code control and set team standards for downloading updated templates. This product offers the capability to track old versions of the template while allowing a developer to “check out” the package for editing and saving.

This additional step in utilizing templates has the potential to save you and your team from a lot of headaches. Keep in mind that using the TFS product will not solve the problem is team standards are not set and adhered to.

Thanks for reading; I hope this has been insightful. Have a great day!