In Microsoft SQL Server Code-Named “Denali” CTP1, Integration Services supports two deployment models, the project deployment model and the legacy deployment model. The project deployment model enables use of parameters and the Integration Services catalog. The catalog and other new features in SQL Server Code-Named “Denali” CTP1 are an evolution of Integration Services deployment and administration in SQL Server. This topic describes the project deployment model and how using it differs from using the legacy deployment model. For more information about parameters and the catalog see Integration Services (SSIS) Parameters and Integration Services (SSIS) Catalog Overview, respectively.

Using the Project Deployment Model

The project deployment model is new in SQL Server Code-Named "Denali" CTP1. The legacy deployment model was available in SQL Server 2008 and SQL Server 2005, and remains in SQL Server Code-Named "Denali" CTP1.  The type of deployment model that you choose for a project determines which development and administrative options are available for that project. The following table shows the differences and similarities between using the project deployment model and using the legacy deployment model. 

When Using the Project Deployment Model... When Using the Legacy Deployment Model...
A project is the unit of deployment. A package is the unit of deployment.
Parameters are used to assign values to package properties. Configurations are used to assign values to package properties.
A project, containing packages and parameters, is built to a project deployment file (.ispac extension). Packages (.dtsx extension) and configurations (.dtsConfig extension) are saved individually to the file system.
A project, containing packages and parameters, is deployed to the Integration Services catalog on an instance of SQL Server. Packages and configurations are copied to the file system on another computer. Packages can also be saved to the MSDB database on an instance of SQL Server.
CLR integration is required on the database engine. CLR integration is not required on the database engine.
Environment-specific parameter values are stored in environment variables. Environment-specific configuration values are stored in configuration files.
Projects and packages in the catalog can be validated on the server before execution. You can use SQL Server Management Studio, stored procedures, or managed code to perform the validation.

Packages are validated just before execution. You can also validate a package with dtExec or managed code.
Packages are executed by starting an execution on the database engine. A project identifier, explicit parameter values (optional), and environment references (optional) are assigned to an execution before it is started. Packages are executed with the dtExec and DTExecUI execution utilities. Applicable configurations are identified by command-prompt arguments (optional).
During exeuction, events that are produced by the package are captured automatically and saved to the catalog. You can query these events with Transact-SQL views. During execution, events that are produced by a package are not captured automatically. A log provider must be added to the package to capture events.
Packages are run in a separate Windows process. Packages are run in a separate Windows process.
SQL Server Agent is used to schedule package execution. SQL Server Agent is used to schedule package execution.

 

Features Available to Projects Developed for the Project Deployment Model

The following features are available only to projects developed for the project deployment model:

  • Parameters: A parameter specifies the data that will be used by a package. You can scope parameters to the package level or project level with package parameters and project parameters, respectively. Parameters can be used in expressions or tasks. When the project is deployed to the catalog, you can assign a literal value for each parameter or use the default value that was assigned at design time. In place of a literal value, you can also reference an environment variable. Environment variable values are resolved at the time of package execution. For more information, see Integration Services (SSIS) Parameters.
  • Environments: An environment is a container of variables that can be referenced by Integration Services projects. Each project can have multiple environment references, but a single instance of package execution can only reference variables from a single environment. Environments allow you to organize the values that you assign to a package. For example, you might have environments named "Dev", "Test", and "Production". For more information, see Integration Services (SSIS) Environment Variables.
  • Environment variables: An environment variable defines a literal value that can be assigned to a parameter during package execution. To use an environment variable, create an environment reference (in the project that corresponds to the environment having the parameter), assign a parameter value to the name of the environment variable, and specify the corresponding environment reference when you configure an instance of execution. For more information, see Integration Services (SSIS) Environment Variables.
  • Integration Services catalog: All Integration Services objects are stored and managed on an instance of SQL Server in a database referred to as the Integration Services catalog. The catalog allows you to use folders to organize your projects and environments. Each instance of SQL Server can have one catalog. Each catalog can have zero or more folders. Each folder can have zero or more projects and zero or more environments. A folder in the catalog can also be used as a boundary for permissions to Integration Services objects. For more information, see Integration Services (SSIS) Catalog Overview.
  • Catalog stored procedures and views: A large number of stored procedures and views can be used to manage Integration Services objects in the catalog. For example, you can specify values to parameters and environment variables, create and start executions, and monitor catalog operations. You can even see exactly which values will be used by a package before execution starts. For more information, see Integration Services (SSIS) Catalog Architecture and API.

Project Deployment Life Cycle

The project deployment life cycle describes how SQL Server features relate to the various stages of Integration Services project deployment. At a high level, SQL Server Business Intelligence Development Studio (BIDS) is used to develop projects and the SQL Server database engine is used to host, execute, and manage projects. At the center of the project deployment model is the project deployment file (.ispac extension). The project deployment file is a self-contained unit of deployment that includes only the essential information about the packages and parameters in the project. The project deployment file does not capture all of the information contained in the Integration Services project file (.dtproj extension). For example, additional text files that you use for writing notes are not stored in the project deployment file and thus are not deployed to the catalog.  

There are four stages of the project deployment life cycle:

  1. Build: Prepares your project for deployment.
  2. Deploy: Adds your project to the Integration Services catalog on an instance of SQL Server.
  3. Import: Loads a project into BIDS from the Integration Services catalog or a project deployment file.
  4. Migrate: Converts legacy packages and configurations so that they can be used in the project deployment model.

The following diagram shows the four stages of the deployment life cycle.

Integration Services (SSIS) Project Deployment Life Cycle

The first stage for a new project is the build phase. In the build phase, a single project, including packages and parameters, is built to a project deployment file (.ispac extension). During the deploy phase, a project is added to a folder in the Integration Services catalog. Once in the catalog, packages in the project can be executed and managed on the server. The import phase is when a project is loaded into BIDS, where it can be updated or used to create another project. In the migration phase, legacy packages and configurations are built to a project deployment file, a format that is compatible with the project deployment model.

More details about each of these phases are described in the following sections.

Build

New Integration Services projects are created and designed in Business Intelligence Development Studio (BIDS). In BIDS, each new Integration Services project uses the project deployment model by default. Within each project you can create multiple packages and parameters. To facilitate deployment of your project, you can identify entry-point packages and provide detailed descriptions for each parameter.

Marking a package as an entry-point package is a way to clearly identify “parent” packages that execute other “child” packages. After the project has been deployed to the Integration Services catalog, the entry point designation helps administrators (who may not be familiar with the project) select the right package to start within an execution.

You can create project parameters and package parameters. For each parameter, you can add detailed descriptions to describe the values that are used by the packages. You can also assign default parameter values that will persist for the life of the project. A default parameter value that is assigned while the project is being designed is referred to as a design default parameter value. If no other values are explicitly assigned to parameters before execution, packages can use the default parameter values. If you require some values to be explicitly assigned, you can mark those parameters as required. Required parameters must have parameter values assigned before the corresponding packages will execute.

When a package or element is executed during development, the corresponding project is built and a project deployment file is created. The project deployment file contains all of the information necessary to run the contained packages, including the corresponding package and parameter information.

Deploy

Use the Integration Services Deployment Wizard to deploy a project to the Integration Services catalog. The deployment source is the project that is being deployed. The deployment destination is the project that will be created or updated after the deployment. The deployment source can be a project deployment file or a project that has already been deployed. The deployment source can come from the same catalog as the destination or from a different catalog on another instance of SQL Server. You can update a project by specifying the same project for the deployment source and destination.

The following diagram shows the different ways the Deployment Wizard can deploy a project.

Integration Services (SSIS) Project Deployment Wizard

Import

Use the Integration Services Import Project Wizard to load a project into BIDS, where it can be updated or used to make another project. You can import from a project deployment file or from a deployed project in the Integration Services catalog.

The following diagram shows the different ways the Import Project Wizard can import a project.

Integration Services (SSIS) Import Project Wizard

Migrate

Use the Integration Services Migration Wizard to migrate legacy packages and configurations to the project deployment model. The wizard performs the following tasks:

  • Creates a new project (that uses the project deployment model).
  • Adds the specified legacy packages to the new project.
  • Helps convert Execute Package task references from external references (referencing a legacy package) to project references (referencing a package in the new project). 
  • Analyzes configurations and helps you create parameters to replace the configurations.
  • Saves the new project to a project deployment file.

After the new project is built to a project deployment file, you can deploy it to the Integration Services catalog.

The following diagram shows how the Migration Wizard migrates legacy packages to a project.

 Integration Services (SSIS) Migration Wizard 

See Also