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!