Creating a Package Template

When you go through the trouble of developing the perfect shell or blueprint of a package to use repeatedly, you don’t want to have to do it again. Templates in SSIS can help you prevent having to do repetitive boilerplate work inside the package (such as setting up your configuration, event handlers, logging, or annotation). They also enforce the same best practices across your entire development team, and strengthen time-to-market and cross-training. As developers move from project to project, they’ll be able to read another developer’s package.

To create a template, you must create the package that you want to use as a template, then copy the package to the following directory:

 %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransfor mationItems

The %ProgramFiles% directory is an environment variable that redirects you to the proper drive for your Program Files folder (and would typically resolve to c:\Program Files).

When the file is copied into each developer’s template folder, they can use the template by right-clicking the project and selecting Add New Item. Then, they select the file name for the template. A new package will be created that is an exact duplicate of the one created earlier.

The problem with this newly created package being exactly like the original package is that the new package has the same ID as its parent. This same predicament occurs when you copy and paste a package to clone a package in Solution Explorer. This is not really an issue until your packages are moved to production. At that point, you’ll have multiple packages with the same PackageID potentially logging in to the same database. If a problem occurs, you won’t be able to trace which package is having the issue, since the PackageID is how you map back to the package’s name.

The problem can easily be fixed by generating a new PackageID in BIDS. You can do this by going to the Properties Window for the package and selecting <Generate New ID> for the ID property. This can also be done with the DtUtil.exe utility by using the –I switch. This particular command line utility is discussed in more detail in Chapter 9.