您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
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，修改parameter的Server value
选择Project，右击弹出快捷菜单，选择Configure，配置parameters
select epv.parameter_name,epv.parameter_value,epv.parameter_data_type,epv.sensitive,epv.requiredfrom catalog.execution_parameter_values epv
4，指定parameter的Execution value
默认情况下，Parameter的Execution value是Parameter的Server value，但是也可以通过Execute指定一个Execution value。
选择package，右键弹出快捷菜单，选择Execute，Parameter 默认的Execution Value是Server value，可以点击 ... 为Parameter设置一个Execution value。
5,Parameter value的有效性
可以通过Validate来检查package的有效性，在package执行之前，验证package的有效性
6，Parameter的信息存储在SSISDB中，实际上，实现的功能和Package Deployment Model下的Configurations是相同的，用于将Package执行的property配置到DB中，在package执行的时候，去读取配置信息，然后执行package；在必要时，修改package的parameter value.
7，使用catalog.set_object_parameter_value 存储过程来修改Parameter 的Server value。
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 ]
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.
修改Parameter 的Server Value的脚本如下：
select * from catalog.object_parameters opselect *from [catalog].[folders]select *from [catalog].[projects]exec catalog.set_object_parameter_value @object_type = 30 , @folder_name =N'TestISProject' , @project_name = N'TestISProject' , @parameter_name = N'ParameterA' , @parameter_value = 9 , @object_name = N'Package1.dtsx' , @value_type = N'V'
Note：使用该存储过程修改的是Parameter的Server value，可以通过视图：catalog.object_parameters 字段 default_value来查看parameter的server value。
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.
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。