Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 33

Making a Package Dynamic with Parameters

SQL 2012 introduced a new parameter paradigm. Parameters enable you to pass in new values for a specific package execution. When your packages are deployed to the new SSIS catalog, an interface is provided to enable you to change the values of parameters prior to running the package. Parameters are very similar to variables, except parameters are easier to change and configure using the new SQL Server Management Studio interface for SSIS.

A parameter, like a variable, is a placeholder that has a name, data type, scope, and value. The value provided in SQL Server Data Tools is called the default or design value. You can replace this value prior to execution. Parameters also have a sensitive property and a required property. When you mark a parameter as sensitive, its value is encrypted in the database and displayed as NULL or *****. When you mark a parameter as required, you must provide the parameter value prior to execution, rather than using the design parameter.

Parameters also have a scope. Parameters can have a package scope or a project scope. A parameter with a project scope can be used in all packages within the project. Package scoped parameters can only be used within the package in which they are defined.

To create a package scoped parameter, choose the Parameters tab in the SSIS designer and create a new parameter by clicking the icon in the top left. Then populate the fields Name, Data Type, Value, Sensitive, Required, ...