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 which are similar in nature 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 SQL Server Books Online (BOL). Following are the links to BOL articles with detailed steps for creating Package Templates:

• 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 Packages Templates:

• 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.

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .