videos
video collection of sqlschool.gr
Summary
In this episode you will learn how to export existing environment variables to migrate to another instance or to copy them.
Video
Video Demo
Demo Script
select * from ssisdb.catalog.projects
select * from ssisdb.catalog.folders
select * from ssisdb.catalog.packages
select * from ssisdb.catalog.environments
select * from ssisdb.catalog.environment_variables
select * from ssisdb.catalog.environment_references
SELECT * FROM ssisdb.[internal].[data_type_mapping]
exec SSISDB.catalog.create_environment
@folder_name=N'demoB',
@environment_name = N'PREPRD'
go
exec ssisdb.catalog.create_environment_variable
@folder_name = N'demoB'
, @environment_name = N'DEV'
, @variable_name = N'DB'
, @data_type = N'String'
, @sensitive = 0
, @value = N'server=.;database=db;'
, @description = N'Connection string';
exec ssisdb.catalog.create_environment_variable
@folder_name = N'demoB'
, @environment_name = N'DEV'
, @variable_name = N'SourceFolder'
, @data_type = N'String'
, @sensitive = 0
, @value = N'e:\source\'
, @description = N'Connection string';
exec ssisdb.catalog.set_environment_variable_property
@folder_name = N'demoB'
, @environment_name = N'DEV'
, @variable_name = N'SourceFolder'
, @property_name='description'
, @property_value='Source folder'
exec ssisdb.catalog.create_environment_variable
@folder_name = N'demoB'
, @environment_name = N'DEV'
, @variable_name = N'DestFolder'
, @data_type = N'String'
, @sensitive = 0
, @value = N'f:\dest\'
, @description = N'Destination folder';
declare @crlf nchar(2) = nchar(13)+nchar(10);
declare @tab nchar(1) = nchar(9);
declare @scriptout nvarchar(max)='';
SELECT
@scriptout += 'EXEC [SSISDB].[catalog].[create_environment_variable]' + @crlf
+ replicate(@tab,3) + ' @folder_name='+ quotename(cast(f.name as nvarchar),'''') + @crlf
+ replicate(@tab,3) + ',@environment_name=N' + quotename(cast(e.name as nvarchar),'''') + @crlf
+ replicate(@tab,3) + ',@variable_name=N' + quotename(cast(v.name as nvarchar),'''') + @crlf
+ replicate(@tab,3) + ',@data_type=N'+ quotename(v.type ,'''')+ @crlf
+ replicate(@tab,3) + ',@sensitive= ' + cast(v.sensitive as nvarchar) + @crlf
+ replicate(@tab,3) + ',@value=' + IIF(v.type = 'String',N'N''' + CONVERT(NVARCHAR(500), v.value) + ''''
,CONVERT(NVARCHAR(500), v.value) ) + @crlf
+ replicate(@tab,3) + ',@description=N' + quotename(cast(v.description as nvarchar),'''') + ';'+ @crlf
+ 'GO' + @crlf
FROM SSISDB.catalog.environments as e
JOIN SSISDB.catalog.folders as f ON f.folder_id = e.folder_id
JOIN SSISDB.catalog.environment_variables as v ON e.environment_id = v.environment_id
WHERE f.name = N'demoB'
AND e.name = N'DEV';
select @scriptout FOR XML PATH('')