Sunday, 10 July 2011

Create a package template in SSIS

Case
I have a package that I want to use as a basis for all my packages. How do I create a package template in SSIS.

Solution
Creating a template for SSIS is very simple:

1) Create package
Create a package that you want to use as a template. Add for example loggings, configurations or connections that you want to use for all packages. Give it a suitable name.

2) Copy package
Copy the saved package to the DataTransformationItems folder from Visual Studio:
C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\
or
C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems\
or
C:\Program Files\Microsoft Visual Studio 8\Common7 \IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems

That's all!

3) Using template
In your project go to the Solution Explorer and right click on the project and choose Add, New Item...
Solution Explorer: Add new item...

















Now choose your template and give the package a suitable name (*).
Select your new template














4) Change ID
Your newly created package gets the same package GUID as the original template file. You have to generate a new GUID for you package. If you don't do that, things like logging could be a problem because you can't distinguish the packages by ID. Also see/vote for this feedback at Microsoft Connect
Generate new ID / GUID










(*) An other bug is that when you enter a new name in the wizard, it only changes the filename and not the internal object name. It's better to just use the given default name and then rename it in the Solution Explorer. Renaming in the Solution Explorer will also give you the option to rename the internal object name.

Note: Changes made in a template file doesn't effect existing packages based on that template.

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.