Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Creating SSIS Package Templates for Reusability

MSSQLTips author Arshad Ali By:   |   Read Comments (10)   |   Related Tips: More > Integration Services Development
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.

Create an SSIS package to be used as template

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

save a copy of this package at the following location based on version of SQL Server you are using

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:

Using the SSIS package in other projects... Reusability

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.

the Add New Item dialog box

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.

 using SQL Server 2012, you need to deploy the template to a different location

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.

Next Steps


Last Update: 1/2/2013


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, November 14, 2014 - 4:22:57 AM - ANITA PAL Read The Tip
Tip Comments Pending Approval

Wednesday, March 06, 2013 - 3:14:51 AM - Sim Read The Tip

Im new in SSIS and my company is already making me to re-design everything which has been badly designed. I always strive for standards so that the next developer can find everything in place and documented. Thanks once again.


Wednesday, March 06, 2013 - 3:12:37 AM - Sim Read The Tip

Thank you very much Arshad and Scott.


Tuesday, March 05, 2013 - 9:52:43 AM - Scott Coleman Read The Tip

Resetting the package GUID from a template is absolutely a requirement, but you really need to reset the GUIDs for every task, connection, and other object in the package.  The easy way to do this is to install the free BIDS Helper utility (http://bidshelper.codeplex.com), which has a "Reset GUIDs" action that will replace them all at once.

A template should also include whatever logging and error handling components are standard in your organization.

Any copied package (whether from a template or any Add Existing Package action) will include all the connection managers, variables, configuration specifications of the original.  But XML configuration files are not copied with the template.  If the XML files are in a common location, such as a network share, they will work in the new template-derived package.  But if you keep the XML files locally with your packages, you'll have to manually copy the file (or "Add existing item") into the project.

I like to use SQL Server configurations, so all configuration works no matter where on the network I want to deploy or develop the packages.


Friday, February 22, 2013 - 11:58:45 AM - Arshad Read The Tip

Yes Sim, your understanding is correct!


Thursday, February 21, 2013 - 10:16:16 AM - Sim Read The Tip

Can i create the same template...but include the connection strings to DB and tasks that are always common. Also will the template retain my XML configuration when added in another BIDS project?


Tuesday, January 15, 2013 - 7:25:51 AM - Elton Read The Tip

thanks man,

simple, objective and very useful.


Monday, January 07, 2013 - 4:16:48 AM - Arshad Read The Tip

Hi Abhijit,

There is no inbuit task in SSIS for data comparison (did you try lookup transformation?) though as SSIS is being extensible you can develop your own custom component for the purpose.

http://msdn.microsoft.com/en-us/library/ms136078.aspx

 

For data comparison you can use Visual Studio data comparison tool as described below:

http://www.mssqltips.com/sqlservertip/2098/sql-data-comparison-with-visual-studio-2010/


Saturday, January 05, 2013 - 5:17:18 AM - Abhijit Read The Tip

Thanks Arshad,

Its very useful & save lot of manual effort.

 

On other note,

WE need some more info. in data Comparision. is there any way where we can compare two database data using SSIS & save output in some table.

 

Thanks,

Abhijit Desai


Wednesday, January 02, 2013 - 9:25:22 AM - John Read The Tip

Can you share the sample package?

 

Thanks!




 
Sponsor Information