/*
This script will take all project parameters with their design time values in your SSIS-project and create them into
SSIS environment variables. Use it at your own risk!
 
Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/
 
DECLARE
    @folder_name nvarchar(200)      = 'ProjectFolderName',
    @environment_name nvarchar(200) = 'DEV',
    @folder_id bigint,
    @parameter_name varchar(200),
    @design_default_value sql_variant,
    @sensitive bit, 
    @description nvarchar(1024), 
    @data_type nvarchar(128)
 
--> Need som metadata
SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)
 
--> Create new environment in given folder if it does not exist
IF NOT EXISTS (SELECT 1 FROM [catalog].[environments] WHERE folder_id = @folder_id AND name = @environment_name)
    EXEC [SSISDB].[catalog].[create_environment] @environment_name=@environment_name, @folder_name=@folder_name
 
--> Create cursor for all project parameters
DECLARE cur CURSOR FOR
    SELECT a.parameter_name, a.design_default_value, a.sensitive, a.description, a.data_type
    FROM [SSISDB].[catalog].[object_parameters] a
        INNER JOIN [SSISDB].[catalog].[projects] b
            ON a.project_id = b.project_id
    WHERE b.folder_id = @folder_id
        AND SUBSTRING(a.parameter_name, 1, 3) <> 'CM.'
    ORDER BY a.parameter_name
 
OPEN cur
FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
 
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        --> Create the environment variable if it doesn't exist
        IF NOT EXISTS (SELECT 1 
                        FROM [SSISDB].[catalog].[folders] a 
                            INNER JOIN [catalog].[environments] b 
                                ON a.folder_id = b.folder_id 
                            INNER JOIN [catalog].[environment_variables] c
                                ON b.environment_id = c.environment_id
                        WHERE a.name = @folder_name
                            AND b.name = @environment_name
                            AND c.name = @parameter_name)
            EXEC [SSISDB].[catalog].[create_environment_variable] 
                @variable_name = @parameter_name, 
                @sensitive = @sensitive, 
                @description = @description, 
                @environment_name = @environment_name, 
                @folder_name = @folder_name, 
                @value = @design_default_value, 
                @data_type = @data_type
 
    FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
    END
 
CLOSE cur
DEALLOCATE cur