Export (0) Print
Expand All

catalog.set_object_parameter_value (SSISDB Database)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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 ]

[ @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).

This stored procedure requires one of the following permissions:

  • READ and MODIFY permissions on the project

  • Membership to the ssis_admin database role

  • Membership to the sysadmin server role

The following list describes some conditions that may cause the stored procedure to raise an error:

  • The parameter type is not valid

  • The project name is not valid

  • For package parameters, the package name is not valid

  • The value type is not valid

  • The user does not have the appropriate permissions

  • 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_parameters 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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft