RSS

Tips ‘N’ Tricks – SSIS – Ensure Consistency across Packages using Template Packages

12 Jun
In one of my previous tip, I explained how we can Ensure Consistency across Multiple Reports using Report Templates. In this tip, let’s see how we can do something similar in SSIS to ensure consistency across multiple packages.
 
I have been working on SQL Server Integration Services (SSIS) for few years now and in most of the 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, Mappings etc. The Structure or Design of those packages is similar in various ways. To name a few:
 
  • Event Handlers like OnPostExecute, OnError etc.
  • Settings like Package ProtectionLevel, DelayValidation, and Checkpoints etc.
  • Use of Naming Conventions Prefixes, Suffixes etc.
  • Package Configurations.
  • List of Common set of Variables and the list goes on.
Interestingly 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.
 
To start building a Package Template, follow these steps:
 
  • Create a New SSIS Package and add all the required features which you would like to be replicated to multiple packages when you create new packages from this Template.
  • Save the Package as say DemoTemplatePackage.dtsx
  • Copy the package to the following location:

<<Installation Directory>>:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

  • This will make DemoTemplatePackage.dtsx available for you to be used multiple times.

To use this package as a Template, follow the below mentioned steps:

  • Right click on your Integration Services Project, go to Add | New Item…
  • In the Add New Item dialog box, you will see that DemoTemplatePackage.dtsx is available as one of the options as shown in the below screenshot. Simply select this Template, Provide a Name which you want for your Target Package and Click Add.

Use of SSIS Package Templates

The above set of steps, simply make a copy of the DemoTemplatePackage.dtsx and add it with a new name (MyFirstDemoPackage.dtsx in the above example) to your project.

Important Points – Following are some of the important points to be noted while using this approach:

  • Generate Unique GUID for every single instance of Template Package (Go to Package Properties, Click the ID drop down and select <Generate New ID>).
  • Once a new packages is created using this Template, any new changes made in the Package Template will not be propagated/replicated to the newly created package.

Advantages – Following are some of the advantages of using Package Templates in SSIS:

  • Ensure consistency in terms of Coding Standards, Naming Standards, and Best Practices etc. in SSIS Packages across different Modules/Projects.
  • Considerable reduction in the Package Development Time.
  • Considerable reduction in the Package Testing Time.

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

What is your approach towards ensuring consistency in SSIS Packages across different Modules/Projects?

Take a look at the other Tips and Tricks in this Series on Tips, Tricks, Techniques, and Shortcuts to Improve Productivity, and Design and Coding Skills.

About these ads
 

Tags: , , , , , , , , ,

3 responses to “Tips ‘N’ Tricks – SSIS – Ensure Consistency across Packages using Template Packages

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: