Pros and Cons of SSIS Templates

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!

How to Create and Use SSIS Templates

SQL Server Integration Services templates can be a great time saver when creating multiple ETL packages for your business intelligence solution. In this video I will demonstrate how to create and use these templates.

First you will start a new project. Open SQL Server Business Development Studio, navigate to File > New > Project.

A New Project window will open. Navigate to Business Intelligence Projects > Integration Services Project, name your project and select OK. The name of your project should correspond to your intended use of the template.

Next you will build your template by adding the items from the toolbox that you wish to include. These should be items that will be needed for each use of the template.

You will  now need to save your project in two different ways. First click File > Save Selected Items. Next click File > Save Copy of “project name” As. A Save Copy of Package dialog box will open. Select File System in the Package location box and navigate to the Package Path field. You will want to save your package in this location: (assuming that you have installed SSIS with default settings) C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\Template Tutorial.dtsx

Now you will be able to access and use your SSIS template. Open a new SSIS project and navigate to the solution explorer pane. Right click on the project and select Add > New Item. This will pop up a box that allows you to select your newly created SSIS template and add it to your package.

There are many great benefits to using SSIS templates as well as some dangers. I will explore each of these areas in my next post. I hope this has been helpful, have a great day!