Skip to content

Latest commit

 

History

History
102 lines (72 loc) · 4.53 KB

catalog-create-environment-variable-ssisdb-database.md

File metadata and controls

102 lines (72 loc) · 4.53 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic
catalog.create_environment_variable (SSISDB Database)
catalog.create_environment_variable (SSISDB Database)
chugugrace
chugu
03/14/2017
sql
integration-services
language-reference

catalog.create_environment_variable (SSISDB Database)

[!INCLUDEsqlserver-ssis]

[!INCLUDE SQL Server]

Create an environment variable in the [!INCLUDEssISnoversion] catalog.

Syntax

catalog.create_environment_variable [ @folder_name = ] folder_name  
    , [ @environment_name = ] environment_name  
    , [ @variable_name = ] variable_name  
    , [ @data_type = ] data_type  
    , [ @sensitive = ] sensitive  
    , [ @value = ] value  
    , [ @description = ] description

Arguments

[@folder_name =] folder_name
The name of the folder that contains the environment. The folder_name is nvarchar(128).

[@environment_name =] environment_name
The name of the environment. The environment_name is nvarchar(128).

[@variable_name =] variable_name
The name of the environment variable. The variable_name is nvarchar(128).

[@data_type =] data_type
The data type of the variable. Supported environment variable data types include Boolean, Byte, DateTime, Double, Int16, Int32, Int64, Single, String, UInt32, and UInt64. Unsupported environment variable data types include Char, DBNull, Object, and Sbyte. The data type of the data_type parameter is nvarchar(128).

[@sensitive =] sensitive
Indicates whether the variable contains a sensitive value or not. Use a value of 1 to indicate that the value of the environment variable is sensitive or a value of 0 to indicate that it is not. A sensitive value is encrypted when it is stored. A value that is not sensitive is stored in plaintext.Sensitive is bit.

[@value =] value
The value of the environment variable. The value is sql_variant.

[@description =] description
The description of the environment variable. The value is nvarchar(1024).

Return Code Value

0 (success)

Result Sets

None

Permissions

This stored procedure requires one of the following permissions:

  • READ and MODIFY permissions on the environment

  • Membership to the ssis_admin database role

  • Membership to the sysadmin server role

Errors and Warnings

The following list describes some conditions that may raise an error or warning:

  • The folder name, environment name, or environment variable name is not valid

  • The variable name already exists in the environment

  • The user does not have the appropriate permissions

Remarks

An environment variable can be used to efficiently assign a value to a project parameter or package parameter for use in the execution of a package. Environment variables enable the organization of parameter values. Variable names must be unique within an environment.

The stored procedure validates the data type of the variable to make sure it is supported by the [!INCLUDEssISnoversion] catalog.

Tip

Consider using the Int16 data type in [!INCLUDEssISnoversion] instead of the unsupported Sbyte data type.

The value passed to this stored procedure with the value parameter is converted from an [!INCLUDEssISnoversion] data type to a [!INCLUDEssNoVersion] data type according to the following table:

Integration Services Data Type SQL Server Data Type
Boolean bit
Byte binary, varbinary
DateTime datetime, datetime2, datetimeoffset, smalldatetime
Double Exact numeric: decimal, numeric; Approximate numeric: float, real
Int16 smallint
Int32 int
Int64 bigint
Single Exact numeric: decimal, numeric; Approximate numeric: float, real
String varchar, nvarchar, char
UInt32 int (int is the closest available mapping to Uint32.)
UInt64 bigint (int is the closest available mapping to Uint64.)