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 ServerDefulatValue from catalog.object_parameters op
select epv.parameter_name,epv.parameter_value,epv.parameter_data_type,epv.sensitive,epv.required from catalog.execution_parameter_values epv
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.
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.
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).
The following Transact-SQL views and stored procedure can be used to display and set parameter values.
Shows the actual parameter values that will be used by a specific execution
Resolves and shows the actual values for the specified package and environment reference
Displays the parameters and properties for all packages and projects in the Integration Services catalog, including the design default and server default values.
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.
If parameter values cannot be resolved, the corresponding package execution will fail. To help avoid failures, you can validate projects and packages by using the Validate 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.
参考文档：
https://msdn.microsoft.com/en-us/library/hh213214.aspx
原文：http://www.cnblogs.com/ljhdo/p/4953714.html