--\ -------------------------------------------------------------------
---) 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

--\ -------------------------------------------------------------------
---) Script to add environment reference to project(s) and to connect
---) environment variables to package parameters, based on the name
--/ -------------------------------------------------------------------
/*
(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 @object_parameter_value_table TABLE(
  [id] int identity(1, 1),
  [object_type] smallint,
  [object_name] nvarchar(260),
  [parameter_name] nvarchar(128),
  [project_name] NVARCHAR(128)
  )

DECLARE @project_names_table TABLE(
  [id] int identity(1, 1),
  [project_name] NVARCHAR(128)
  )

DECLARE @message nvarchar(255)

DECLARE @id int
DECLARE @max_id int
DECLARE @folder_name sysname = N'unknown'
DECLARE @environment_name nvarchar(128) = N'unknown'
DECLARE @environment_description nvarchar(128) = N'unknown'
DECLARE @project_name NVARCHAR(128)

DECLARE @object_type SMALLINT
DECLARE @object_name NVARCHAR(260)
DECLARE @value_type CHAR(1)
DECLARE @parameter_name NVARCHAR(128)
DECLARE @parameter_value SQL_VARIANT

DECLARE @reference_id BIGINT

--\
---) Environment settings
--/

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 @folder_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

INSERT INTO @project_names_table([project_name])
  SELECT
      p.name
  FROM
      [SSISDB].[internal].[folders] f
  JOIN
      [SSISDB].[internal].[environments] e
      ON e.folder_id = f.folder_id
  JOIN
      [SSISDB].[internal].[projects] p
      ON p.folder_id = f.folder_id
  WHERE
      f.name = @folder_name
      AND e.environment_name = @environment_name
      -- Optional project_name filter here:
      -- AND p.name IN ('Project_1', 'Project_2')

--\
---) Add environment reference to project(s).
--/
SELECT @id = 1, @max_id = MAX([id]) FROM @project_names_table
WHILE @id <= @max_id
BEGIN
    SELECT
      @project_name = v.[project_name]
    FROM @project_names_table v
    WHERE [id] = @id;

    IF NOT EXISTS( SELECT 1
        FROM
            [SSISDB].[internal].[folders] f
        JOIN
            [SSISDB].[internal].[environments] e
            ON e.folder_id = f.folder_id
        JOIN
            [SSISDB].[internal].[projects] p
            ON p.folder_id = f.folder_id
        JOIN
            [SSISDB].[internal].[environment_references] r
            ON r.environment_name = e.environment_name
            AND p.project_id = r.project_id
        WHERE
            f.name = @folder_name
            AND e.environment_name = @environment_name
            AND p.name = @project_name
        )
    BEGIN
        SET @message = 'An environment reference for project “' + @project_name + '” is being created.'
        RAISERROR(@message , 0, 1) WITH NOWAIT;

        EXEC [SSISDB].[catalog].[create_environment_reference]
            @environment_name=@environment_name, @reference_id=@reference_id OUTPUT,
            @project_name=@project_name, @folder_name=@folder_name, @reference_type='R';
        --Select @reference_id
    END

    SET @id = @id + 1
END

--\
---) Connect environment variables to package parameters, based on the name
--/
INSERT INTO @object_parameter_value_table (
    [object_type],
    [object_name],
    [parameter_name],
    [project_name]
    )
SELECT
    prm.[object_type],
    prm.[object_name],
    prm.[parameter_name],
    prj.name
FROM
    [SSISDB].[internal].[folders] f
JOIN
    [SSISDB].[internal].[environments] e
    ON e.folder_id = f.folder_id
JOIN
    [SSISDB].[internal].[environment_variables] ev
    ON e.environment_id = ev.environment_id
JOIN
    [SSISDB].[internal].[projects] prj
    ON prj.folder_id = f.folder_id
JOIN
    @project_names_table prjsel
    ON prjsel.project_name = prj.name
JOIN
    [SSISDB].[internal].[object_parameters] prm
    ON prj.project_id = prm.project_id
    AND prm.parameter_name = ev.name
WHERE
    prm.[value_type] != 'R'
    AND prm.value_set = 0
    AND prm.[parameter_name] NOT LIKE 'CM.%'
    AND LEFT(prm.[parameter_name], 1) != '_' -- Naming convention for internally used parameters: start with _
    AND NOT ( prm.[object_type] = 30 AND LEFT(prm.[object_name], 1) = '_') -- Naming convention for internally used SSIS Packages: start with _
    AND f.name = @folder_name
    AND e.environment_name = @environment_name
ORDER BY
    prm.object_name, prm.parameter_name

SELECT @id = 1, @max_id = MAX([id]) FROM @object_parameter_value_table
WHILE @id <= @max_id
BEGIN
    SELECT
        @object_type = v.[object_type],
        @object_name = v.[object_name],
        @parameter_name = v.[parameter_name],
        @project_name = v.[project_name]
    FROM
        @object_parameter_value_table v
    WHERE
        [id] = @id;

    SELECT @value_type = 'R', @parameter_value = @parameter_name;
    
    SET @message = 'Parameter “' + @parameter_name + '” (of object “' + @object_name + '”) is mapped to environment variable.'
    RAISERROR(@message , 0, 1) WITH NOWAIT;

    EXEC [SSISDB].[catalog].[set_object_parameter_value]
      @object_type, @folder_name, @project_name, @parameter_name,
      @parameter_value, @object_name, @value_type

    SET @id = @id + 1
END

RAISERROR('Adding environment reference to project(s) and mapping environment variables to package parameters has completed ..', 0, 1) WITH NOWAIT;

Finally:

GO