Shulou(Shulou.com)06/01 Report--

One, SSIS Parameter Value's type

There are three types of Value for a Parameter, which are Design Value,Server Value,Execution Value.

Design Value refers to the design value, the Default value specified for a Parameter when designing a package in SSDT.

Server value: once package is deployed on Sql Server, a Server Value is generated. The default Server Value is the same as Design Value. You can modify the Server Value of Parameter through SSMS.

Execution Value: when Package executes, the value of Parameter is Execution Value. Normally, when Package executes, it reads Server Value as the Execution value of parameter. When the Execution Value of Parameter is set through Execute, Package will use this value as Execution value, and the Execution value specified in Execute will not override Server value, only for this Execution.

1. View Design Value and Server Value through the catalog.object_parameters view

Select op.parameter_id,op.parameter_name,op.object_type,op.object_name, op.data_type,op.required,op.sensitive,op.design_default_value,op.default_value as ServerDefulatValuefrom catalog.object_parameters op

2. Modify the Server value of parameter

Select Project, right-click the pop-up shortcut menu, select Configure, and configure parameters

3. View Execution value via catalog.execution_parameter_values

Select epv.parameter_name,epv.parameter_value,epv.parameter_data_type,epv.sensitive,epv.requiredfrom catalog.execution_parameter_values epv

4, specify the Execution value of the parameter

By default, the Execution value of Parameter is the Server value of Parameter, but you can also specify an Execution value through Execute.

Select package, right-click to pop up shortcut menu, select Execute,Parameter default Execution Value is Server value, you can click. Set up an Execution value for Parameter.

5The validity of the Parameter value

You can check the validity of package through Validate, and verify the validity of package before package execution.

6Parametric information is stored in SSISDB. In fact, the function implemented is the same as that of Configurations under Package Deployment Model. It is used to configure the property executed by Package into DB, read the configuration information when package executes, and then execute package; to modify the parameter value of package if necessary.

7. Use catalog.set_object_parameter_value stored procedures to modify the Server value of Parameter.

Sets the value of a parameter in the Integration Services catalog. Associates the value to an environment variable or assigns a literal value that will be used by default if no other values are assigned.

Set_object_parameter_value [@ object_type =] object_type, [@ folder_name =] folder_name, [@ project_name =] project_name, [@ parameter_name =] parameter_name, [@ parameter_value =] parameter_value [, [@ object_name =] object_name] [, [@ value_type =] value_type]

Arguments

[@ object_type =] object_type

The type of parameter. Use the value 20 to indicate a project parameter or the value 30 to indicate a package parameter. The object_type is smallInt.

[@ folder_name =] folder_name

The name of the folder that contains the parameter. The folder_name is nvarchar (128).

[@ project_name =] project_name

The name of the project that contains the parameter. The project_name is nvarchar (128).

[@ parameter_name =] parameter_name

The name of the parameter. The parameter_name is nvarchar (128).

[@ parameter_value =] parameter_value

The value of the parameter. The parameter_value is sql_variant.

[@ object_name =] object_name

The name of the package. This argument required when the parameter is a package parameter. The object_name is nvarchar (260).

[@ value_type =] value_type

The type of parameter value. Use the character V to indicate that parameter_value is a literal value that will be used by default of no other values are assigned prior to execution. Use the character R to indicate that parameter_value is a referenced value and has been set to the name of an environment variable. This argument is optional, the character V is used by default. The value_type is char (1).

Remarks

If no value_type is specified, a literal value for parameter_value is used by default. When a literal value is used, the value_set in the object_param eters view is set to 1. A NULL parameter value is not allowed.

If value_type contains the character R, which denotes a referenced value, parameter_value refers to the name of an environment variable.

The value 20 may be used for object_type to denote a project parameter. In this case, a value for object_name is not necessary, and any value specified for object_name is ignored. This value is used when the user wants to set a project parameter.

The value 30 may be used for object_type to denote a package parameter. In this case, a value for object_name is used to denote the corresponding package. If object_name is not specified, the stored procedure returns an error and terminates.

The script to modify the Server Value of Parameter is as follows:

Select * from catalog.object_parameters opselect * from [catalog]. [folders] select * from [catalog]. [projects] exec catalog.set_object_parameter_value @ object_type = 30, @ folder_name = Noble TestISProject`, @ project_name = Noble TestISProject`, @ parameter_name = Noble ParameterA', @ parameter_value = 9, @ object_name = Noble package 1.dtsx', @ value_type = Noble V'

Note: the Server value of Parameter is modified using this stored procedure. You can view the server value of parameter through the view: catalog.object_parameters field default_value.

Second, Parameter Values type

You can assign up to three different types of values to a parameter. When a package execution is started, a single value is used for the parameter, and the parameter is resolved to its final literal value.

The following table lists the types of values.

Value Name

Description

Type of value

Execution Value

The value that is assigned to a specific instance of package execution. This assignment overrides all other values, but applies to only a single instance of package execution.

Literal

Server Value

The value assigned to the parameter within the scope of the project, after the project is deployed to the Integration Services server. This value overrides the design default.

Literal or Environment Variable Reference

Design Value

The value assigned to the parameter when the project is created or edited in SQL Server Data Tools. This value persists with the project.

Literal

You can use a single parameter to assign a value to multiple package properties. A single package property can be assigned a value only from a single parameter.

Executions and Parameter Values

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 execution parameter values. You can also modify the parameters values for existing executions.

When you explicitly set an execution parameter value, the value is applicable only to that particular instance of execution. The execution value is used instead of a server value or a design value. If you do not explicitly set an execution value, and a server value has been specified, the server value is used.

When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.

Environment Variables

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 specify the environment reference for an execution by using the Execute dialog box

If a project parameter references an environment variable and the literal value from the variable cannot be resolved at execution, the design value is used. The server value is not used.

To view the environment variables that are assigned to parameter values, query the catalog.object_parameters view. For more information, see catalog.object_parameters (SSISDB Database).

Determining Execution Parameter Values

The following Transact-SQL views and stored procedure can be used to display and set parameter values.

Catalog.execution_parameter_values (SSISDB Database) (view)

Shows the actual parameter values that will be used by a specific execution

Catalog.get_parameter_values (SSISDB Database) (stored procedure)

Resolves and shows the actual values for the specified package and environment reference

Catalog.object_parameters (SSISDB Database) (view)

Displays the parameters and properties for all packages and projects in the Integration Services catalog, including the design default and server default values.

Catalog.set_execution_parameter_value (SSISDB Database)

Sets the value of a parameter for an instance of execution in the Integration Services catalog.

You can also use the Execute Package dialog box in SQL Server Data Tools (SSDT) modify the parameter value. For more information, see Execute Package Dialog Box.

You can also use the dtexec / Parameter option to modify a parameter value. For more information, see dtexec Utility.

Parameter Validation

If parameter values cannot be resolved, the corresponding package execution will fail. To help avoid failures, you can validate projects and packages by using theValidate dialog box in SQL Server Data Tools (SSDT). 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, see Validate Dialog Box.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.