Template Packages in SSIS

I have been working on SSIS for few years now and in couple of projects I have encountered a situation where in we need to load the data from Source to Staging area or from Feed Files to Staging area requiring similar structure for all the Staging ETL's except for the difference in Naming, Tables, Columns etc.

Interestingly SQL Server/SSIS provides a very great feature which can be utilized effectively to build large number of ETL's (similar ETL's) except for the differences in the tables, connections etc. This feature of SSIS is called as Package Template.

There is a detailed step by step procedure documented in the Microsoft Knowledge Base Articles. Following are the links to Microsoft Knowledge Base articles with detailed steps for creating Template Packages:

• http://msdn.microsoft.com/en-us/library/ms345191.aspx
• http://support.microsoft.com/kb/908018

Advantages of Template Packages in SSIS

• Considerable reduction in the development time
• Ensuring Coding/Naming Standards/Best Practices accross the Modules/Projects/Organization

Here are few tips to make effective use of Template Packages:

• Generation of UniqueID for evey single instance of Template Package
• Changing the necessary connections, configurations, protection levels etc
• Names in the template package should be generic. So that when we instantiate a package from template we can easily rename the new instance simply by doing a find and replace in the XML view of the dtsx packages. Example: Package Name as PKG_PRJ_TableName.dtsx, CheckPoint File Name CHK_PRJ_TableName.chk etc.

Hope you will find this post useful.