Configuring Projects and Packages Using Parameters

Configuring Projects and Packages Using Parameters

Rate This
  • Comments 2

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.

clip_image002

In SSMS, these parameters would be available when you configure or execute a package.

clip_image004

The project parameters can be created and are available in the Project.params window which can be opened from the solution explorer.


clip_image007

clip_image009

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:

@[$<<Project/Package>>::<<ParameterName>>]

E.g.

clip_image011

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.

Other Resources

Leave a Comment
  • Please add 3 and 4 and type the answer here:
  • Post
  • I like this feature very much while calling child package with parameter...nice and simplified feature.

  • pics are not clear and we need some more info how we can make pkg connection strings using parameters instead xml config

Page 1 of 1 (2 items)