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('')