Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Creating SSIS Package Templates for Reusability


By:   |   Read Comments (14)   |   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:





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





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, August 11, 2016 - 12:21:14 PM - Angela Back To Top

 

This is awesome! Works on SQL2014 with SSMS/SSDT 2013 too, just use Visual Studio 12.0 folder


Saturday, November 21, 2015 - 12:31:36 PM - Reuben Anderson Back To Top

It's taken me ages to figure this out.. 

In VS2013, the path is; 

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

I would've thought MSDN would make this easier to find.  


Tuesday, September 22, 2015 - 11:00:34 PM - Reza Back To Top

 

in this demo, actually the template was not used. it is only a empty shell. moreover, each BI project feed Dimensions and fact tables in the different prespective. however, in this demo assimption was feeding Dim. first and then fact tables. finally, in real BI project staging tables are the major key points, which were missed in this empty shell.

Thanks again for your article.

 


Wednesday, June 24, 2015 - 8:51:27 AM - Tushar Verma Back To Top

Hi Arshad,

 

If I need to execute a package created using already created template via a SQL Job, do I need to save the template on the server as well?


Thursday, March 19, 2015 - 2:06:39 PM - Barani Back To Top

I am using SQL server 2008R2 Enterprise Edition, i could not find my template in visual studio templates while adding new item.. Any help is much appreciated...


Wednesday, March 06, 2013 - 3:14:51 AM - Sim Back To Top

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 Back To Top

Thank you very much Arshad and Scott.


Tuesday, March 05, 2013 - 9:52:43 AM - Scott Coleman Back To Top

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 Back To Top

Yes Sim, your understanding is correct!


Thursday, February 21, 2013 - 10:16:16 AM - Sim Back To Top

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 Back To Top

thanks man,

simple, objective and very useful.


Monday, January 07, 2013 - 4:16:48 AM - Arshad Back To Top

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 Back To Top

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 Back To Top

Can you share the sample package?

 

Thanks!


Learn more about SQL Server tools