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.
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:
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:
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.
|
|
|
|
Before the project is moved |
After the project is moved |
Absolute Reference:
the reference remains unchanged
|
|
|
|
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:
Then the value of the environment variable could be used in package validation and execution as shown in figure 5.
Figure 5 : Use environment variable in package execution