Tips ‘N’ Tricks – SSIS – Ensure Consistency across Packages using Template Packages
Event Handlers like OnPostExecute, OnError etc.
Settings like Package ProtectionLevel, DelayValidation, and Checkpoints etc.
Use of Naming Conventions Prefixes, Suffixes etc.
List of Common set of Variables and the list goes on.
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.
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.
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.
- Tips ‘N’ Tricks – SSIS – Quickest way to add an existing SSIS Package to a Project (dattatreysindol.com)
- Tips ‘N’ Tricks – SSIS – Define you own Keyboard Shortcuts in SSIS Package Designer (dattatreysindol.com)
- Tips ‘N’ Tricks – SSIS – Update Properties of Multiple Tasks in One-Go (dattatreysindol.com)
Posted on June 12, 2012, in Best Practices, Development Tips, SQL Server, SSIS, SSIS Tips, Tips 'N' Tricks and tagged Best Practices, Development Tips, Microsoft Visual Studio, MSBI, Packages, SQL Server, SQL Server Business Intelligence Development Studio, SSIS, Templates, Tips and Tricks. Bookmark the permalink. 3 Comments.