-
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.
-
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.
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.
Related articles
- 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)
2 Responses to Tips ‘N’ Tricks – SSIS – Ensure Consistency across Packages using Template Packages