creating SSIS Package templates for reusability

Thursday, 29 August 2013

creating SSIS Package templates for reusability

Problem

We often need to create similar SSIS packages which contain some common components such as connection managers, data flow components, log providers, event handlers, etc... So do we really need to create a package from scratch each time and add all these commonly used components in each package again and again. Is it possible to create a SSIS package with a basic structure/workflow and common components which can be used as template to create subsequent packages?

Solution

SSIS allows creating a package with a basic structure/workflow that has common components which can be further leveraged as a template for reusability or for creating new packages based on this template. This means you are not required to add each item over and over again for each package that you create. This reduces the time needed for development and makes packages look/behave consistently.

Creating a Package to be used as Template

To create a SSIS package to be used as template you have to follow the same approach as creating a new package. You need to use Business Intelligence Development Studio (BIDS) to create a new project of type "Integration Services Project". Create a new package, specify an appropriate name for this package and add the work flow and components you want to be part of the template.

For example, I have a sample package below in which the first task logs the start of the ETL batch. Next I have a container which will eventually contain components for loading data into staging. After that I have another container which will contain components for data loading into dimensions and facts and for cube processing. At the end, it will log success or failure for the package.

Once you are done with creating the basic structure of the package and have added the common components, you need to save a copy of this package at the following locations based on the version of SQL Server you are using:

For SQL Server 2005
<<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
OR
<<Installation drive>>:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

For SQL Server 2008
<<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
OR
<<Installation drive>>:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

For SQL Server 2012
<<Installation drive>>:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems
OR
<<Installation drive>>:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

You need to specify the drive location where Business Intelligence Development Studio (BIDS) or SQL Server Data Tools (SSDT) has been deployed. Please note, as BIDS or SSDT runs locally on client machine, you need to copy the template package to the above location on all the development machines you want it to use. For this example we are naming the template package "SamplePackageTemplate.dtsx".

You are not restricted to deploying only one template. You can deploy as many templates as you want to the folders listed above and reuse them as needed.

Using the SSIS Template in Other Projects

In a new or existing project where you want to add this SSIS package template, you just need to right click on the project name in the Solution Explorer, click on Add > New Item as shown below:

In the Add New Item dialog box, you will notice the deployed package template as shown below. You can select it and specify a name for the package for which the template will be used and click on the Add button to add the new package to your project based on the selected template. That's all you have to do. You now have a package that is pre-configured and you can now customize it for your specific need. Please note, the modifications that are done in the new package do not impact the deployed template as we are working with a copy of the template which is now part of the current project and not the template itself.

If you are using SQL Server 2012, when you add a new item you will see the template appearing in the Add New Item dialog box as shown below. Select the template and specify the name for the new package which will be based on this template.

Generate Unique ID

If you are using SQL Server 2005 or 2008 then you should generate a unique ID. This is recommended because it helps in analyzing log data with better differentiation for each package. To generate a unique ID value for the package, click the ID in the Properties pane, and then click Generate New ID.

In SQL Server 2012 when you add a package based on a template, SSDT generates a unique ID for each package and hence you don't need to do it separately.