Cacher is the code snippet organizer for pro developers

SET NOCOUNT ON
 
DECLARE
    @folder_name nvarchar(200)              = 'InnoLog', --SSISDB folder where environment ligger i
    @environment_name_current nvarchar(200) = 'Prod', -- the environment that needs to be scriptet
    @environment_name_new nvarchar(200)     = 'Test', -- the name of the new environment the script will create
    @name sysname, 
    @sensitive bit, 
    @description nvarchar(1024), 
    @value sql_variant, 
    @type nvarchar(128)
 
PRINT 'DECLARE 
    @folder_id bigint,
    @environment_id bigint'
 
PRINT ''
 
--> Create folder if it doesn't exist and get folder_id
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')
    EXEC [SSISDB].[catalog].[create_folder] @folder_name=N''' + @folder_name + ''', @folder_id=@folder_id OUTPUT
ELSE
    SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')'
 
PRINT ''
 
--> Create environment if it doesn't exist
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = N''' + @environment_name_new + ''')
    EXEC [SSISDB].[catalog].[create_environment] @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''''
 
PRINT ''
 
--> Get the environment_id
PRINT 'SET @environment_id = (SELECT environment_id FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id and name = N''' + @environment_name_new + ''')'
 
PRINT ''
 
--> Making cursor because mapping of sql_variant datatype is different than the normal datatypes
DECLARE cur CURSOR FOR
    SELECT c.name, c.sensitive, c.description, c.value, c.type
    FROM [SSISDB].[catalog].[folders] a
        INNER JOIN [SSISDB].[catalog].[environments] b
            ON a.folder_id =  b.folder_id
        INNER JOIN [SSISDB].[catalog].[environment_variables] c
            ON b.environment_id = c.environment_id
    WHERE a.name = @folder_name
        AND b.name = @environment_name_current
 
OPEN cur
FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type
 
PRINT 'DECLARE @var sql_variant'
PRINT ''
 
WHILE (@@FETCH_STATUS = 0)
    BEGIN
	--Add an IF foreach datatype that i translates wrong
		IF @type = 'DateTime'
        PRINT 'SET @var = CONVERT(datetime, ''' + CONVERT(nvarchar(max) , @value, 120) + ''', 120)'
		ELSE IF @type = 'Boolean'
        PRINT 'SET @var = CONVERT(bit, ''' + CONVERT(nvarchar(max) , @value) + ''')'
		ELSE IF @type = 'Int32'
        PRINT 'SET @var = ' + CONVERT(nvarchar(max), @value) + ''
		ELSE
		PRINT 'SET @var = N''' + CONVERT(nvarchar(max), @value) + ''''
		
		
        PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environment_variables] WHERE environment_id = @environment_id AND name = N''' + @name + ''')
            EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N''' + @name + ''', @sensitive=' + CONVERT(varchar(2), @sensitive) + ', @description=N''' + @description + ''', @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''', @value=@var, @data_type=N''' + @type + ''''
 
        PRINT ''
 
    FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type
    END
 
CLOSE cur
DEALLOCATE cur