--\ -------------------------------------------------------------------
---) Script to create folder, environment and settings.
--/ -------------------------------------------------------------------
/*
(c) Copyright 2016 - hansmichiels.com
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/
USE [SSISDB]
GO
DECLARE @environment_variables_table TABLE(
[id] int identity(1, 1),
--)> Just an autonumber to be able to go through the table without cursor.
[variable_name] nvarchar(128),
--) Name of the variable
[data_type] nvarchar(128),
--) Variable datatype e.g. [ Boolean | Byte | DateTime | Decimal | Double | Int16 | Int32 | Int64 | SByte | Single | String | UInt32 | UInt64 ]
--) Check all possible values using this query: SELECT DISTINCT [ssis_data_type] FROM [SSISDB].[internal].[data_type_mapping]
[sensitive] bit,
--) Indication of the environment variable is sensitive (e.g. a password).
[value] nvarchar(4000),
--) The variable value.
[description] nvarchar(1024),
--) Extra description for the variable.
[create_only] bit
--) Indication that a variable should only be created when it does not exist yet, but should not be replaced when it already exists.
--) (so the value in the script does not overwrite the existing value).
--) In this way you can prevent later configuration changes in an environment being undone by the values in the script.
)
DECLARE @id int
DECLARE @max_id int
DECLARE @folder_id int
DECLARE @folder_name sysname = N'unknown'
DECLARE @environment_name nvarchar(128) = N'unknown'
DECLARE @environment_description nvarchar(128) = N'unknown'
DECLARE @variable_name nvarchar(128)
DECLARE @data_type nvarchar(128)
DECLARE @sensitive bit
DECLARE @value sql_variant
DECLARE @description nvarchar(1024)
DECLARE @create_only bit --
DECLARE @exists bit
DECLARE @nsql nvarchar(max)
DECLARE @message nvarchar(255)
DECLARE @false BIT = 0, @true BIT = 1
--\
---) Environment settings.
---) This is a generic script for all environments, using the @@SERVERNAME (SQL Server Instance name)
---) to set values right for each environment.
--/
IF @@SERVERNAME LIKE 'VWS63-SQL1%1'
BEGIN
SELECT
@folder_name = N'DEV',
@environment_name = N'DEV',
@environment_description = N'My development environment'
END
IF @@SERVERNAME = '<SQL INSTANCE NAME>'
BEGIN
SELECT
@folder_name = N'TST',
@environment_name = N'TST',
@environment_description = N'My test environment'
END
IF @@SERVERNAME = '<SQL INSTANCE NAME>'
BEGIN
SELECT
@folder_name = N'PRD',
@environment_name = N'PRD',
@environment_description = N'My production environment'
END
IF @environment_name = N'unknown'
BEGIN
RAISERROR('Unknown environment', 16, 1)
-- Make sure that the script does not continue when the environment settings could not be set due to an unknown @@SERVERNAME.
GOTO Finally
END
----\
-----) Create folder, when needed.
----/
IF NOT EXISTS (
SELECT 1
FROM [SSISDB].[internal].[folders] f
WHERE f.name = @folder_name
)
BEGIN
SET @message = 'Folder “' + @folder_name + '” is being created.'
RAISERROR(@message , 0, 1) WITH NOWAIT;
EXEC [SSISDB].[catalog].[create_folder] @folder_name, @folder_id OUTPUT;
END
--\
---) Create environment, when needed.
--/
IF NOT EXISTS (
SELECT 1
FROM [SSISDB].[internal].[environments] e
JOIN [SSISDB].[internal].[folders] f
ON e.folder_id = f.folder_id
WHERE e.[environment_name] = @environment_name
AND f.name = @folder_name
)
BEGIN
SET @message = 'Environment “' + @environment_name + '” is being created.'
RAISERROR(@message , 0, 1) WITH NOWAIT;
EXEC [SSISDB].[catalog].[create_environment] @folder_name, @environment_name, @environment_description;
END
IF @environment_name = N'DEV'
BEGIN
INSERT INTO @environment_variables_table
( [create_only], [variable_name], [data_type], [sensitive], [value], [description] )
VALUES
-- Just a few examples of environment variables, add your own here or generate this part from existing environments or deployed packages, see scripts below.
( @true, N'Parameter1', N'Int32', @false, N'1', N'Example parameter 1 (DEV).' )
, ( @true, N'Parameter2', N'String', @false, N'Hello world from script (DEV).', N'Example parameter 2 (DEV).' )
, ( @true, N'Parameter3', N'Decimal', @false, N'9.95', N'Example parameter 3 (DEV).' )
, ( @true, N'ProjectParameter1', N'String', @false, N'Hello world from script (DEV).', N'Example projectparameter 1 (DEV).' )
, ( @true, N'ProjectParameter2', N'Int32', @false, N'4', N'Example projectparameter 2 (DEV).' )
END
IF @environment_name = N'TST'
BEGIN
INSERT INTO @environment_variables_table
( [create_only], [variable_name], [data_type], [sensitive], [value], [description] )
VALUES
-- Just a few examples of environment variables, add your own here or generate this part from existing environments or deployed packages, see scripts below.
( @true, N'Parameter1', N'Int32', @false, N'1', N'Example parameter 1 (TST).' )
, ( @true, N'Parameter2', N'String', @false, N'Hello world from script (TST).', N'Example parameter 2 (TST).' )
, ( @true, N'Parameter3', N'Decimal', @false, N'9.95', N'Example parameter 3 (TST).' )
, ( @true, N'ProjectParameter1', N'String', @false, N'Hello world from script (TST).', N'Example projectparameter 1 (TST).' )
, ( @true, N'ProjectParameter2', N'Int32', @false, N'4', N'Example projectparameter 2 (TST).' )
END
IF @environment_name = N'PRD'
BEGIN
INSERT INTO @environment_variables_table
( [create_only], [variable_name], [data_type], [sensitive], [value], [description] )
VALUES
-- Just a few examples of environment variables, add your own here or generate this part from existing environments or deployed packages, see scripts below.
( @true, N'Parameter1', N'Int32', @false, N'1', N'Example parameter 1 (PRD).' )
, ( @true, N'Parameter2', N'String', @false, N'Hello world from script (PRD).', N'Example parameter 2 (PRD).' )
, ( @true, N'Parameter3', N'Decimal', @false, N'19.95', N'Example parameter 3 (PRD).' )
, ( @true, N'ProjectParameter1', N'String', @false, N'Hello world from script (PRD).', N'Example projectparameter 1 (PRD).' )
, ( @true, N'ProjectParameter2', N'Int32', @false, N'4', N'Example projectparameter 2 (PRD).' )
END
SELECT @id = 1, @max_id = MAX([id]) FROM @environment_variables_table
WHILE @id <= @max_id
BEGIN
SELECT
@variable_name = v.variable_name,
@data_type = v.data_type,
@sensitive = v.sensitive,
@value = v.value,
@description = v.[description],
@create_only = v.[create_only],
@exists = 0
FROM @environment_variables_table v
WHERE [id] = @id;
IF EXISTS (
SELECT 1
FROM
[SSISDB].[internal].[environment_variables] v
JOIN
[SSISDB].[internal].[environments] e ON e.environment_id = v.environment_id
JOIN
[SSISDB].[internal].[folders] f
ON e.folder_id = f.folder_id
WHERE
v.[name] = @variable_name
AND e.environment_name = @environment_name
AND f.name = @folder_name
)
BEGIN
IF @create_only = 1
BEGIN
SET @message = @variable_name + ' already exists and is not replaced.'
RAISERROR(@message , 0, 1) WITH NOWAIT;
SET @exists = 1;
END ELSE BEGIN
SET @nsql = N'EXECUTE [catalog].[delete_environment_variable] '
+ N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
+ N'@variable_name = N''' + @variable_name + ''''
PRINT @nsql;
EXEC sp_executesql @nsql;
END
END
IF @exists = 0
BEGIN
SET @message = 'Creating variable “' + @variable_name + '”.';
RAISERROR(@message , 0, 1) WITH NOWAIT;
SET @nsql = N'EXECUTE [catalog].[create_environment_variable] '
+ N'@folder_name = N'''+ @folder_name + ''', @environment_name = N''' + @environment_name + ''', '
+ N'@variable_name = N'''+ @variable_name + ''', @data_type = N''' + @data_type + ''', '
+ N'@sensitive = ' + CONVERT(NVARCHAR, @sensitive) + ', @description = N''' + @description + ''', '
+ CHAR(13) + CHAR(10) + N'@value = ' +
CASE UPPER(@data_type)
WHEN 'String' THEN 'N''' + CONVERT(NVARCHAR(1000), @value) + ''' '
ELSE CONVERT(NVARCHAR(1000), @value)
END + '; '
-- PRINT @nsql;
EXEC sp_executesql @nsql;
END
SET @id = @id + 1
END
RAISERROR('Creating folder, environment and settings has completed ..', 0, 1) WITH NOWAIT;
Finally:
GO
--\
---) ON a server where the VARIABLES already exist, you can use this query to generate the 'VALUES' part of the INSERT command used above.
--/
/*
DECLARE @environment_name nvarchar(128) = N'DEV'
SELECT
m.*,
v.name,
N', ( N''' + v.name + ''', N''' + m.[ssis_data_type] + ''', '
+ CONVERT(NCHAR(1), v.sensitive) + ', N'''
+ CASE v.sensitive
WHEN 0 THEN CONVERT(NVARCHAR(4000), v.value)
ELSE '< type sensitive value here TODO >' END
+ ''', N''' + v.description + ''' )' AS [sql_values_part]
FROM
[SSISDB].[internal].[environment_variables] v
JOIN
[SSISDB].[internal].[environments] e
ON e.environment_id = v.environment_id
JOIN
[SSISDB].[internal].[folders] f
ON e.folder_id = f.folder_id
JOIN
[SSISDB].[internal].[data_type_mapping] m
ON m.sql_data_type = v.base_data_type
AND ( m.sql_data_type != 'tinyint' OR m.ssis_data_type = 'Byte')
WHERE
e.environment_name = @environment_name
*/
--\
---) ON a server where the PACKAGES are ALREADY DEPLOYED, you can use this query to generate the 'VALUES' part of the INSERT command used above.
---) Manually check the output before running the sql script!
--/
/*
SELECT
N', ( N''' + q.[variable_name] + ''', N''' + q.[data_type] + ''', '
+ CONVERT(NCHAR(1), q.[sensitive]) + ', N'''
+ CASE q.sensitive
WHEN 0 THEN CONVERT(NVARCHAR(4000), q.value)
ELSE '< type sensitive value here TODO >' END
+ ''', N''' + q.[description] + ''' )' AS [sql_values_part]
FROM
(
SELECT TOP 2147483647
prm.[parameter_name] AS [variable_name],
prm.[parameter_data_type] AS [data_type],r
prm.[sensitive],
COALESCE(prm.[default_value], prm.[design_default_value]) AS [value],
prm.[description],
ROW_NUMBER() OVER (PARTITION BY prm.[parameter_name] ORDER BY prm.[parameter_name]) AS [Occurrence]
FROM
[SSISDB].[internal].[object_parameters] prm
-- [variable_name], [data_type], [sensitive], [value], [description]
WHERE
prm.[value_type] = 'V'
AND prm.[parameter_name] NOT LIKE 'CM.%'
AND LEFT(prm.[parameter_name], 1) != '_'
AND NOT ( prm.[object_type] = 30 AND LEFT(prm.[object_name], 1) = '_') -- Naming convention for internally used SSIS Packages: start with _
ORDER BY
prm.[parameter_name]
) q
WHERE
q.Occurrence = 1
*/
GO