woensdag 24 november 2010

SQL11 (Denali) CTP1 Create Environments and Environments variables with T-SQL

Looking at the current CTP I found that I could not find the Package Configurations any more. Package configurations seems to be replaced by Package Parameters. The relation to make these parameters dynamic is the Environment configuration in the SSIS Catalog.

Because I like to make i possible to script everything to be able to deploy or duplicate environments for disaster recovery or just simple fall back I did take a look at how to use T-SQL to script SSIS Enviroments.
All steps are based on that the SSIS Catalog already exists.

There a in basic 4 stored procedures that can be used for creating and deleting the environment and environment variables.

First step is to create the environment:

 [catalog].[create_environment] 
@environment_name=(nvarchar(128))
, @environment_description=(nvarchar(1024))
, @folder_name=(nvarchar(128))'

Second step is to create the environment variable:
 [catalog].[create_environment_variable] 
@variable_name=(nvarchar(128))
, @sensitive=(bit)
, @description=(nvarchar(1024))
, @environment_name=(nvarchar(128))
, @folder_name=(sysname)
, @value=(sql_variant)
, @data_type=(nvarchar(128))

When deleting it is possible to delete the environment and all environment variables with the delete_environment stored procedure.
When you want to delete a single environment variable just use:

[catalog].[delete_environment_variable]
@variable_name=(nvarchar(128))
, @environment_name=(nvarchar(128))
, @folder_name=(nvarchar(128))

Delete the enviromments
[catalog].[delete_environment] 
@environment_name=(nvarchar(128))
, @folder_name=(nvarchar(128))

Here are some samples how to add and drop Environments and Enviroment variables using the given stored procedures.

USE SSISDB
GO

--- Create a new Environment
/*
catalog.create_environment
@folder_name = (nvarchar(128)
*/
IF NOT EXISTS (SELECT 1 FROM catalog.environments WHERE name = N'envFirstLook')
BEGIN
EXEC [catalog].[create_environment]
@environment_name=N'envFirstLook'
, @environment_description=N'This is my first Integration Services Environment'
, @folder_name=N'FirstLook'
PRINT 'Environment is created'
END
ELSE
BEGIN
PRINT 'Environment already exists'
END

--- Create a new Environment Variable
IF NOT EXISTS (SELECT 1 FROM catalog.environment_variables WHERE name = N'MyFirstEnvironmentVariable')
BEGIN
DECLARE @var sql_variant = N'This is the value of the environment variable'
EXEC [catalog].[create_environment_variable]
@variable_name=N'MyFirstEnvironmentVariable'
, @sensitive=False
, @description=N'Why is this a Environment Variable and not Parameter?'
, @environment_name=N'envFirstLook'
, @folder_name=N'FirstLook'
, @value= @var
, @data_type=N'String'
PRINT 'Environment variable is created'
END
ELSE
BEGIN
PRINT 'Environment variable already exists'
END

--- Drop environment variable
IF EXISTS (SELECT 1 FROM catalog.environment_variables WHERE name = N'MyFirstEnvironmentVariable')
BEGIN
EXEC [catalog].[delete_environment_variable]
@variable_name=N'MyFirstEnvironmentVariable'
, @environment_name=N'envFirstLook'
, @folder_name=N'FirstLook'
PRINT 'Environment variable is deleted'
END
ELSE
BEGIN
PRINT 'Environment variable not found, nothing to drop'
END

--- Drop environment
IF EXISTS (SELECT 1 FROM catalog.environments WHERE name = N'envFirstLook')
BEGIN
EXEC [catalog].[delete_environment]
@environment_name=N'envFirstLook'
, @folder_name=N'FirstLook'
PRINT 'Environment is deleted'
END
ELSE
BEGIN
PRINT 'Environment not found, nothing to drop'
END

Geen opmerkingen: