Today’s post is from Rakesh Parida, an SDET on the SQL Server Integration Services team.
With SQL Server ‘Denali’, we introduce the new paradigm of parameters. Parameters are objects within the SSIS packages whose values can be predetermined or easily configured while executing the package. These parameters can be used to configure runtime behavior of package executions with the new server based storage model of SSIS packages; parameters are an efficient way to configure runtime values of package or project properties.
Types of Parameters
With the new concept of Projects in SSIS, we can have parameters at package as well as project scopes. A package parameter would be scoped and be available for the respective package execution while a project parameter would be available for the execution for any package within the project.
Package & Project Parameters in BIDS and SSMS
The package parameters can be created and are available in the parameters tab within the package window.
In SSMS, these parameters would be available when you configure or execute a package.
The project parameters can be created and are available in the Project.params window which can be opened from the solution explorer.
Use of package and project parameters
Parameters are useful for providing runtime values to properties during a package execution. Hence, in a way they replace the concept of configurations that we had for SSIS packages.
Package parameters are useful for providing values to specific package executions. As they are package scope, they would only be available to the package in which they were created.
Project parameters are available to all the packages in a project. They are useful for configuring values which can be shared between packages. For instance if you wanted to have a single parameter containing a server name to be used by multiple packages, then Project Parameters will be useful for you.
You can use a parameter anywhere in an expression by referring it using the following syntax:
The evaluation order is similar to using a variable in an expression and then executing a package. Hence, the value gets evaluated and assigned to the property at Validation phase.