In Microsoft SQL Server Code-Named “Denali” CTP1, Integration Services (SSIS) projects can be developed for the project deployment model or the legacy deployment model. When you develop your project for the project deployment model, your project can use parameters and be deployed to the Integration Services catalog. These new features are an evolution of Integration Services deployment and administration in SQL Server. This topic describes Integration Services projects that are developed for the project deployment model. For more information about the project deployment model, see Integration Services (SSIS) Project Deployment Overview.
In the project deployment model, an Integration Services project is a unit of deployment. A project is a versioned container for parameters and packages. Each time you deploy a project to the catalog, the version of the project in the catalog automatically changes. After a project has been deployed to an Integration Services catalog on an instance of SQL Server, the project can also contain environment references. For more information about the catalog, see Integration Services (SSIS) Catalog Overview.
Conceptually, a project is a single unit of deployment. Physically, it can take three forms:
The following image shows a logical representation of the objects in a project in the Integration Services catalog.
As with previous versions of Integration Services, you create projects in BIDS. To use parameters and the Integration Services catalog, your project must be configured to use the project deployment model. This is the default configuration for new projects. Use BIDS to create one or more packages in the project. If you have multiple packages in your project and intend for one "parent" package to execute other "child" packages, mark the parent package as an entry-point package. Marking a package as an entry-point package helps the maintainability of your project. by clearly identifying the "starting" package. To mark a package as an entry-point package, in the BIDS Solution Explorer, right-click the package and then click Entry-point Package so that a check mark appears.
You can also create multiple project-level and package-level parameters when you design a project for the project deployment model. When you create parameters in BIDS you have the option of specifying default parameter values for those parameters. Default parameter values specified in BIDS are referred to as design defaults. Design defaults persist with the package for the lifetime of the project. For more information, see Integration Services (SSIS) Parameters.
Each time you execute a package or a portion of a package in your project, BIDS automatically builds the project and creates a project deployment file (.ispac extension). You can also build your project by clicking the build selection from the Build menu or by pressing F5.
Use the Integration Services Import Project Wizard to load projects into BIDS that have already been deployed to a catalog or built to a project deployment file. Import a project when you want to update a deployed project or use a deployed project as the basis to create a new project. Open the Import Project Wizard from the File | New | Project menu. Then, in the Business Intelligence Projects templates, click Integration Services Import Project Wizard.
Use the Integration Services Deployment Wizard to add your project to the Integration Services catalog on an instance of SQL Server. When you deploy a project with the wizard, you can specify new default parameter values that only apply to that version of the project while it is in the catalog. Default parameter values specified for a project in the catalog are referred to as the server defaults. If available at the time of package execution, server defaults are used instead of design defaults when no other literal values are specified for a parameter. For more information about parameters, see Integration Services (SSIS) Parameters.
If the package you're deploying has parameters, the Integration Services Deployment Wizard allows you to specify environment variables for the server defaults. For more information about environment variables, see Integration Services (SSIS) Environment Variables.
Each time you deploy a project to the same folder in the same catalog with the same name, the catalog retains the previous version. By default, it will save a total of ten versions of a project. If you want to "roll back" a project deployment by restoring an earlier version, you can restore it with SQL Server Management Studio (SSMS) or Transact-SQL.
To see previous versions of a project in SSMS Object Explorer, click the Previous Versions page in the Project Properties dialog box. To restore a selected version, right-click it and then click Restore this version. In Transact-SQL, use the view catalog.object_versions to see previous versions, and the stored procedure catalog.restore_project to restore previous versions.
After a project is in the catalog, you can add multiple environment references to the project. Environments allow you to organize groups of variables, the values of which can be referenced by parameters. An environment reference acts as a bridge between an environment and a project. When a parameter references an environment variable, the value of the variable is resolved through the environment reference at the time of package execution. A project can have multiple environment references, but only a single reference can be used during a single instance of package execution. The following image demonstrates how an environment reference enables the values of two parameters to be resolved during package execution. For more information about environments, see Integration Services (SSIS) Environment Variables.
In SQL Server Management Studio (SSMS), you can manage your projects from the Integration Services node in Object Explorer. Under the catalog, named SSISDB, you can create multiple folders. Within a folder, expand Projects and Environments to see all projects and environments that are associated with the folder. For more information about the catalog, see Integration Services (SSIS) Catalog Overview.
The following image shows a folder in the catalog named Folder1, with a project named Integration Services Project1 and two environments, named e1 and e2:
The Integration Services catalog is a user database that provides a comprehensive set of views and stored procedures. You can use Transact-SQL for managing projects and other objects in the catalog. For more information, see Integration Services (SSIS) Catalog Architecture and API.
When using Integration Services in SQL Server Code-Named "Denali" CTP1, consider the following:
For information about Integration Services security with respect to the catalog, see Integration Services (SSIS) Security in SQL Server.
Integration Services (SSIS) Project Deployment Overview Integration Services (SSIS) Catalog Overview Integration Services (SSIS) Catalog Architecture and API Integration Services (SSIS) Projects Integration Services (SSIS) Parameters Integration Services (SSIS) Environment Variables Integration Services (SSIS) Package Execution Integration Services (SSIS) Project and Package Validation Integration Services (SSIS) Security in SQL Server Monitoring Operations in the Integration Services (SSIS) Catalog Integration Services (SSIS) Catalog Identifiers