How to create a package template in SQL Server Business Intelligence Development Studio (908018)


The information in this article applies to:

  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition

INTRODUCTION

Microsoft SQL Server 2005 includes SQL Server Integration Services (SSIS). SSIS includes SQL Server Business Intelligence Development Studio. SQL Server Business Intelligence Development Studio is a development environment that you use to create SSIS packages. Typically, an SSIS package contains items such as connection managers, log providers, and dataflow tasks.

To copy items between packages, you can copy the complete package to a new file location, or you can create a package template. This article describes how to create a package template. The package template includes all the items in the original package. However, the package template can be reused to create other packages. To create a new package, add or remove items from the package template.

MORE INFORMATION

An SSIS package can contain items such as connection managers, log providers, control flow elements, dataflow elements, event handlers, variables, and configurations. You can reuse these items when you use a package template to create a new package. For example, you may want to reuse the following items in a package template:
  • Log providers: You can create a package that includes a connection manager and a log provider. You can also use that package as a template for other packages. When you do this, all event information is logged in the same SQL Server database.
    Note It may be easier to analyze log data when all event information is logged in the same SQL Server database.
  • Common Execute SQL tasks: You can create a package that truncates tables or attaches databases.
  • Send Mail tasks: You can create a package that contains an SMTP connection manager, a Send Mail task, and a property expression to build the Subject line. Use this package as a template to create other packages that notify you by e-mail when the package runs successfully or generates an error.

Create a new package template in SQL Server Business Intelligence Development Studio

  1. Start SQL Server Business Development Studio.
  2. Click File, point to New, and then click Project.
  3. In the New Project window, click Business Intelligence Projects, click Integration Services Project, type a name for the project, and then click OK.

    Note We recommend that you type a name that describes the functionality of the package.
  4. Add the items that you want from the Toolbox to the Package.dtsx file.
  5. Click File, and then click Save Selected Items.

    Note You can save an empty package.
  6. Click File, and then click Save Copy of filename As. In this instance, filename is the name of the file.

    Note You can copy the package to the folder location in step 7 instead of using the Save Copy of filename As command.
  7. In the Save Copy of Package dialog box, click File System in the Package location box, type the following path in the Package path box, and then click OK. In this path, drive is the hard disk where Microsoft Visual Studio 2005 is installed:

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

    Note If you did not use the default location to install Visual Studio 2005, type the path of the Visual Studio 2005 folder in the Package path box.

Use the package template in another solution or project

  1. Start SQL Server Business Development Studio.
  2. Click File, point to New, and then click Project.
  3. In the New Project window, click Business Intelligence Projects, click Integration Services Project, type a name for the project, and then click OK.

    Note We recommend that you type a name that describes the functionality of the package.
  4. In Solution Explorer, right-click the project name, point to Add, and then click New Item.
  5. Under Visual Studio installed templates, click the template that you want, type a name for the template, and then click Add.

    Note The default name for the template in the Name box is the name of the template plus an incremented counter. For example, if the template name is Test.dtsx, the default name is Test1.dtsx.

    Note We recommend that you generate a unique ID property value for the package to make analyzing log data easier. To generate a unique ID property value, click the ID in the Properties pane, and then click Generate New ID. If Generate New ID does not appear in the Properties pane, verify that the package properties appear in the Properties pane. If the properties for an item such as a connection manager or task appear in the Properties pane, Generate New ID is unavailable.

REFERENCES

For more information about how to regenerate the package ID of an SSIS package that was duplicated, click the following article number to view the article in the Microsoft Knowledge Base:

906564 How to use SQL Server Business Intelligence Development Studio or the dtutil utility to regenerate the package ID of an SSIS package that was duplicated

Modification Type:MinorLast Reviewed:2/24/2006
Keywords:kbsql2005ssis kbhowto kbinfo KB908018 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.