Today’s post is from Renhe Li – a developer on the SSIS team based in Shanghai.

-----------------

Server Environments are a new concept introduced in SQL Server Denali for the Integration Services Catalog. It is a container for “server variables”. Each folder in the IS Server contains a number of environments.

When you add an environment to a folder, you are basically exposing a set of values and allowing them to be used when the package is executed or validated in IS Catalog. You can create an environment on server under the environment node as shown in Figure 1.

clip_image001

Figure 1: Creating a server environment

We could also use T-SQL stored procedure to do this directly:

create_environment [ @folder_name = ] folder_name
                 , [ @environment_name = ] environment_name
               [ , [ @environment_description = ] environment_description ]

A Server Environment name should be unique under the same folder. The access of the environment is controlled by SSIS_Admin, so you could only view the environments you have permission to.

Server Variables

After the environment is created, you could add environment variables into that container. We list the basic information of an environment variable in the below table:

Environment Variable Properties 

Property name

Description

Data Type

Note

Environment_id

Unique identifier for the environment variable to indicate which environment it belongs to

BigInt

 

Name

Name of the environment variable

nvarchar (255)

The environment variable name should be unique under the same environment

Type

Type of environment variable

nvarchar(128)

Type supported:

Boolean, byte, datetime, decimal, double, int16, int32, int64, sbyte, single, string, uint32, uint64

Sensitive

Whether the environment variable contains a sensitive value

Bit

Sensitive values are encrypted in IS catalog

Value

The value of the environment variable

Sql_variant

Only non-sensitive variable are shown here.

You could add, remove or modify an environment variable by invoking server APIs: catalog.create_environment_variable, catalog.delete_environment_variable etc. or you could do this directly in SSMS as shown in Figure 2:

clip_image002

Figure 2: Create server variables

Project Environment Reference

The project would not use any of the environments by default, and in order to use the value of the environment variable, you need to specify the reference between project and environment.

You could create a project environment reference by using SSMS UI:

image

Figure 3: Create the project environment reference

A project-environment reference can be marked as relative or absolute when it is created: The relative reference means the project will use the specified environment under the same folder and absolute reference means the project will use environment pointed by {environment folder name, environment name}.

Figure 4 shows the difference between relative and absolute reference when we move the project from Test folder to Production folder:

Relative Reference: The reference is changed to use the environment with same name under production folder.

clip_image011

clip_image013

Before the project is moved

After the project is moved

Absolute Reference: the reference remains unchanged

clip_image014

clip_image016

Before the project is moved

After the project is moved

Figure 4: Difference between absolute and relative reference when moving a project

After the reference is created, you could invoke the following API, with the value_type set to ‘R’ to specify a referenced value for a package parameter:

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 ]

Example
A project Project1 contains a package - P1. The project is stored in a folder “Finance”.
P1 has two parameters: start_date.
If you want to set the value of P1.start_date to contain a referenced value, you can invoke the set_object_parameter_value API as follows directly:

exec set_object_parameter_value 30, 'Finance', 'project1', 'start_date', 'env_variable1', 'P1', 'R'

Then the value of the environment variable could be used in package validation and execution as shown in figure 5.

clip_image018

Figure 5: Use environment variable in package execution