In Microsoft SQL Server Code-Named “Denali” CTP1, Integration Services (SSIS) introduces parameters. Parameters allow you to assign values to properties within packages at the time of package execution. Parameters are available only to projects developed for the project deployment model. You can create parameters at the project level and at the package level, referred to as project parameters and package parameters, respectively.
When using the project deployment model, projects are deployed to the Integration Services catalog, where a comprehensive set of views and stored procedures can be used to manage parameters and the literal values that they provide to packages. In SQL Server Code-Named “Denali” CTP1, Business Intelligence Development Studio (BIDS) and SQL Server Management Studio (SSMS) also support parameters.
This topic describes what parameters are and how they are used by projects developed for the project deployment model. For more information about the project deployment model, see Integration Services (SSIS) Project Deployment Overview.
Parameters allow you to assign values to properties within your packages. You can use a single parameter to assign a value to multiple package properties, but a single package property can only be assigned a value from a single parameter. Depending on where the project is in the project deployment life cycle, you can assign up to three different types of values to each parameter. The three types are listed here in the order in which they can be applied to the parameter:
This example describes a parameter named pkgOptions that is used to specify options for the package in which it resides. The following image shows the values that were assigned to the parameter at three different points in time: design, deployment, and execution.
During design time, when the parameter was created in Business Intelligence Development Studio (BIDS), a default value of 1 was assigned to the parameter. This default value is referred to as the design default. If the project was deployed to the catalog and no other values were assigned to this parameter, the package property corresponding to the pkgOptions parameter would be assigned the value of 1 during package execution. The design default persists with the project throughout its life cycle.
During deployment of the project with the Integration Services Deployment Wizard, a value of 3 is assigned to the pkgOptions parameter. This new default value is referred to as the server default because it is applicable only while the corresponding project resides in the catalog. If no other values were assigned to this parameter, the package property corresponding to the pkgOptions parameter would be assigned the value of 3 during package execution.
Note: Server defaults can also be specified or cleared after the project has been deployed to the catalog. For more information about project deployment, see Integration Services (SSIS) Project Deployment Overview.
While preparing a specific instance of package execution, a value of 5 is assigned to the pkgOptions parameter. This value is referred to as the execution value because it applies to the parameter only for that particular instance of execution. Execution parameter values override server defaults and design defaults. When execution starts, the package property corresponding to the pkgOptions parameter is assigned the value of 5. For more information about package execution with the Integration Services catalog, see Integration Services (SSIS) Package Executions.
Use Business Intelligence Development Studio (BIDS) to create parameters. In BIDS, there are different windows for creating project parameters and package parameters. The Parameters and Variables - <package name> window is for package parameters and the Parameters - <project name> window is for project parameters. The following image shows the package parameter named pkgOptions being created in the parameters and variables window.
Note: Only projects developed for the project deployment model can use parameters. This is the default setup for new projects in SQL Server Code-Named "Denali" CTP1. For more information about projects, see Integration Services (SSIS) Projects.
To create or edit package parameters in BIDS, click on the package design surface and then under the SSIS menu, click Parameters and Variables. You can also click the Parameters and Variables button on the top-right corner of the package designer surface (the button looks like a blue cube).
To create or edit project parameters in BIDS, open the Project menu and then click Project Parameters. You can also right-click the project in Solution Explorer and then click Project Parameters.
When you create a parameter in BIDS, there are several properties to specify:
Note: When you deploy a project to the catalog, several more properties become associated with the project. To see all properties for all parameters in the catalog, use the catalog.object_parameters view. For more information, see the view documentation at catalog.object_parameters.
If you are migrating a legacy project to the project deployment model, you can use the Integration Services Migration Wizard to help you create parameters based on configurations. The following image shows how the wizard helps you migrate your legacy packages and configurations to a project deployment file.
After the project has been saved to a project deployment file, you can deploy the project to the Integration Services catalog or edit the project further in BIDS. For more information about the project deployment file, see Integration Services (SSIS) Project Deployment Overview.
When you deploy projects to the catalog with the Integration Services Deployment Wizard, you can assign default parameter values that apply only while the project resides in the catalog. These values are referred to as server defaults and override the design defaults. The design defaults persist with the project and can be viewed at any time, but if a server default is available, it will be used instead of a design default.
Note: Server defaults can be added, edited, or cleared at any time after the project has been deployed.
Server defaults can be literal values or environment variable references. A literal value is the end value that you want to be used by the package when it executes. An environment variable reference is the name of an environment variable that resides in an environment in the catalog. When the package is executed, the corresponding environment variable value (a literal value) is referenced by the parameter and assigned to the corresponding property in the package. For more information about environments, see Integration Services (SSIS) Environment Variables.
In SQL Server Code-Named “Denali” CTP1, Integration Services introduces the execution object. The execution is an object that represents a single instance of package execution. When you create an execution, you specify all of the details necessary to run a package, such as the project name and the name of the package that should be started first (the entry-point package). If any parameters use environment variables, you must also specify an environment reference to use so that the appropriate environment variable values can be bound to the corresponding parameters.
When an execution is started, all parameters are resolved to their final literal values. If a parameter references an environment variable, the literal value from that variable is resolved through the specified environment reference and applied to the parameter. The final literal parameter value that is used for package execution is referred to as the execution parameter value.
You can also explicitly set an execution parameter value before the execution is started, after the execution has been created. When you set an execution parameter value, that value overrides any default parameter values and is used for package execution.
For more information about executions, see Integration Services (SSIS) Package Executions.
A parameter can have up to three different values associated with it: the design default, the server default, and the execution parameter value. When the parameter is not marked as required, only one of these values needs to be specified for the package to execute. When multiple values are present, an order of precedence determines which of the values will be used. The following table shows the order of parameter value precedence.
When an execution parameter value is set explicitly, that value will be used during execution instead of a server default or design default value. If only a server default and a design default have been specified for a parameter, the server default value will be used.
Note: If a parameter is marked as required, a server default value or execution parameter value must be specified for that parameter for the corresponding package to execute. Required parameters can have design defaults, but they are not sufficient for package execution.
Parameter values are not bound until package execution starts. The final literal value that is used during execution, the execution parameter value, is determined by the configuration of the corresponding execution. To see execution parameter values, server defaults, and design defaults, you can run Transact-SQL queries against the catalog. You can also use SQL Server Management Stuido to see execution parameter values.
The following Transact-SQL views and stored procedure can be used to display parameter values:
To information about using SQL Server Management Stuido to see execution parameter values, see Monitoring Operations in the Integration Services (SSIS) Catalog.
The Deployment Wizard allows you to set server default parameter values when you deploy your project to the catalog. After your project is in the catalog, you can use SQL Server Management Studio (SSMS) Object Explorer or Transact-SQL to set server default values.
To set server defaults with SSMS Object Explorer:
To set server defaults with Transact-SQL, use the catalog.set_object_parameter_value stored procedure. To view current server defaults, query the catalog.object_parameters view. To clear a server default value, use the catalog.clear_object_parameter_value stored procedure.
When you explicitly set a parameter value for an instance of execution, that value is only applicable to that particular instance of execution. To set an execution parameter value with Transact-SQL, use the catalog.set_execution_parameter_value stored procedure. To view current execution parameter values, query the catalog.execution_parameter_values view.
Note: Execution parameter values can only be set with Transact-SQL.
If parameter values cannot be resolved, the corresponding package execution will fail. To help avoid failures, you can validate projects and packages. Validation allows you to confirm that all parameters have the necessary values or can resolve the necessary values with specific environment references. Validation also checks for other common package issues. For more information about validation, see Integration Services (SSIS) Project and Package Validation.
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