SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Create environment variables from project paramete
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 05/28/2013 :  08:32:53  Show Profile  Reply with Quote
SSIS 2012 has limited capabilities of matching project parameters and environment variables so here is a script that does it for you (from http://thefirstsql.com/2013/05/28/ssis-2012-create-environment-variables-from-project-parameters/ )

/*
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


- Lumbago
My blog-> http://thefirstsql.com
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000