Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Create environment variables from project paramete

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2013-05-28 : 08:32:53
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
   

- Advertisement -