Creating & Using a Package Template (SQL Server Integration Services)

When we create an Integration Services solution, a package (package.dtsx) gets added to the solution automatically. This package uses the default package template, which is empty with no control flow task. Also when we add a new package (SSIS Packages folder) or add a new item & select new package, a package with the default template gets added.

Now sometimes like in a big project, we may need to have reusable package tasks, connection managers etc and want to use them as a template for all the newly added packages. This may be required to have the pre & post execution tasks like making entry in the transaction log at the beginning & at the end of the package to impose consistency across packages. So in this case we would want to have a template package which should get added when we add a new package and which we can modify or add other tasks to as per the requirements.

Save & Use a package as a package template:

As stated above, we can use custom packages as templates in SSIS. There are few steps to be followed to save & then use a package as a template.

  1. Create a package (MyPackagetemplate.dtsx) to be used as a template.
  2. Copy this package to the DataTransformationItems folder residing at the location:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject \DataTransformationItems

      3.   Now while adding a new item to the integration services solution as shown below, the copied package will be available as a template in the templates pane:

Add Template

Add Template

 

 

 

 

 

 

 

 

 

Select Template
Select Template

And doing so, the newly added package will be added with all the tasks/transformations etc existing in the template package. And thus we need not to worry about the location of the reusable package while adding as it is always available as a template.

Point to be specified:

When we add an existing template or package, the package ID of the newly added package would remain same as that of the template package. This might be an issue if we are referring them in the log/error (sysdtslog90) tables. But we can change the Package ID by selecting ‘Generate New ID’ option as shown below.

3
Advertisements
This entry was posted in SQL Server, SSIS. Bookmark the permalink.

Leave a Reply

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