Case
I created a new environment in the Integration Services Catalogs, but I can't copy it. If I want a duplicate / clone the enviroment then I have to create it from scratch with all the variables. Where is the copy option?
 |
| Where is the copy environment option? |
Solution
Well there isn't one yet (please see/vote
this Microsoft Connect suggestion). So I copied the create_environment stored procedure and altered it to a copy_environment stored procedure. You have to know the environment id from the environment you want to copy. Double click the existing environment and get the number from the Identifier property.
 |
| Get identifier of existing environment |
Add the new stored procedure and execute it like:
EXEC [catalog].[copy_environment] 3, 'Test', 'Test environment';
 |
The result: a new environment with
same variables as 'Development' |
PS you still have to reference the enviroment to your project manually:
Update: I adjusted the stored procedure and now it also links the new environment to the same project as the existing environment.
 |
| Reference the enviroment to your project |
The new stored procedure (use at own risk!):
USE [SSISDB]
GO
-- USE AT OWN RISK! This stored procedure is altered from create_environment that was shipped with:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
-- Oct 19 2012 13:38:57
-- Copyright (c) Microsoft Corporation
-- Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [catalog].[copy_environment]
--@folder_name nvarchar(128), -- Changed from parameter to variable
@current_environment_id bigint, -- Added to store the ID of the environment you want to copy
@new_environment_name nvarchar(128), -- Renamed (added new_)
@new_environment_description nvarchar(1024)= NULL -- Renamed (added new_)
WITH EXECUTE AS 'AllSchemaOwner'
AS
SET NOCOUNT ON
DECLARE @folder_id bigint
DECLARE @folder_name nvarchar(128) -- Added (moved from parameter)
DECLARE @new_environment_id bigint -- Renamed (added new_)
DECLARE @result bit
DECLARE @caller_id int
DECLARE @caller_name [internal].[adt_sname]
DECLARE @caller_sid [internal].[adt_sid]
DECLARE @suser_name [internal].[adt_sname]
DECLARE @suser_sid [internal].[adt_sid]
EXECUTE AS CALLER
EXEC [internal].[get_user_info]
@caller_name OUTPUT,
@caller_sid OUTPUT,
@suser_name OUTPUT,
@suser_sid OUTPUT,
@caller_id OUTPUT;
IF(
EXISTS(SELECT [name]
FROM sys.server_principals
WHERE [sid] = @suser_sid AND [type] = 'S')
OR
EXISTS(SELECT [name]
FROM sys.database_principals
WHERE ([sid] = @caller_sid AND [type] = 'S'))
)
BEGIN
RAISERROR(27123, 16, 1) WITH NOWAIT
RETURN 1
END
REVERT
IF(
EXISTS(SELECT [name]
FROM sys.server_principals
WHERE [sid] = @suser_sid AND [type] = 'S')
OR
EXISTS(SELECT [name]
FROM sys.database_principals
WHERE ([sid] = @caller_sid AND [type] = 'S'))
)
BEGIN
RAISERROR(27123, 16, 1) WITH NOWAIT
RETURN 1
END
--IF (@folder_name IS NULL OR @environment_name IS NULL) -- Changed, not checking the folder_name any more. Renamed @environment_name to @new_environment_name
IF (@new_environment_name IS NULL)
BEGIN
RAISERROR(27138, 16 , 6) WITH NOWAIT
RETURN 1
END
IF [internal].[is_valid_name](@new_environment_name) = 0
BEGIN
RAISERROR(27142, 16, 1, @new_environment_name ) WITH NOWAIT
RETURN 1
END
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @tran_count INT = @@TRANCOUNT;
DECLARE @savepoint_name NCHAR(32);
IF @tran_count > 0
BEGIN
SET @savepoint_name = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'');
SAVE TRANSACTION @savepoint_name;
END
ELSE
BEGIN TRANSACTION;
BEGIN TRY
EXECUTE AS CALLER
--SET @folder_id = (SELECT [folder_id] FROM [catalog].[folders] WHERE [name] = @folder_name) -- Changed, getting the folder_id via the environment_id
SET @folder_id = (SELECT [folder_id] FROM [internal].[environments] WHERE [environment_id] = @current_environment_id)
SET @folder_name = (SELECT [name] FROM [catalog].[folders] WHERE [folder_id] = @folder_id) -- Added, filling the folder_name which was a parameter
REVERT
IF @folder_id IS NULL
BEGIN
RAISERROR(27104 , 16 , 1, @folder_name) WITH NOWAIT
END
EXECUTE AS CALLER
SET @result = [internal].[check_permission]
(
1,
@folder_id,
100
)
REVERT
IF @result = 0
BEGIN
RAISERROR(27209 , 16 , 1, @folder_name) WITH NOWAIT
END
IF EXISTS(SELECT env.[environment_name]
FROM [internal].[folders] fld INNER JOIN [internal].[environments] env
ON fld.[folder_id] = env.[folder_id] AND
fld.[name] = @folder_name AND
env.[environment_name] = @new_environment_name)
BEGIN
RAISERROR(27157 , 16 , 1, @new_environment_name) WITH NOWAIT
END
-- CHANGED, old Insert query
--INSERT INTO [internal].[environments]
-- VALUES (@environment_name, @folder_id, @environment_description, @caller_sid, @caller_name, SYSDATETIMEOFFSET())
--
--SET @environment_id = SCOPE_IDENTITY()
------------------------------------------------------------------------------------------------------------
-- NEW INSERT INTO QUERIES
------------------------------------------------------------------------------------------------------------
-- Copy the environment
INSERT INTO [internal].[environments]
(
[environment_name]
, [folder_id]
, [description]
, [created_by_sid]
, [created_by_name]
, [created_time]
)
SELECT @new_environment_name as [environment_name] -- My new environment name from the parameter
, [folder_id]
, @new_environment_description as [description] -- My new environment description from the parameter
, [created_by_sid]
, [created_by_name]
, SYSDATETIMEOFFSET() as [created_time]
FROM [internal].[environments]
WHERE [environment_id] = @current_environment_id -- My existing environment from the parameter
-- Get ID from the new environment
SET @new_environment_id = SCOPE_IDENTITY()
-- Copy the variables from the existing environment to the new environment
INSERT INTO [internal].[environment_variables]
(
[environment_id]
, [name]
, [description]
, [type]
, [sensitive]
, [value]
, [sensitive_value]
, [base_data_type]
)
SELECT @new_environment_id as [environment_id] -- Id from the new environment
, [name]
, [description]
, [type]
, [sensitive]
, [value]
, [sensitive_value]
, [base_data_type]
FROM [internal].[environment_variables]
WHERE [environment_id] = @current_environment_id -- My existing environment from the parameter
-- Reference this new environment to the same project
INSERT INTO [internal].[environment_references]
(
[project_id]
, [reference_type]
, [environment_folder_name]
, [environment_name]
, [validation_status]
, [last_validation_time]
)
SELECT p.project_id
, r.reference_type
, r.environment_folder_name
, @new_environment_name as environment_name -- My new environment name from the parameter
, r.validation_status
, r.last_validation_time
FROM [internal].[environment_references] as r
INNER JOIN [internal].[projects] as p
on r.project_id = p.project_id
INNER JOIN [internal].[folders] as f
on p.folder_id = f.folder_id
INNER JOIN [internal].[environments] as e
on e.folder_id = f.folder_id
and e.environment_name = r.environment_name
WHERE e.environment_id = @current_environment_id -- My existing environment id from the parameter
------------------------------------------------------------------------------------------------------------
-- END NEW INSERT INTO QUERIES
------------------------------------------------------------------------------------------------------------
DECLARE @sqlString nvarchar(1024)
DECLARE @key_name [internal].[adt_name]
DECLARE @certificate_name [internal].[adt_name]
DECLARE @encryption_algorithm nvarchar(255)
SET @encryption_algorithm = (SELECT [internal].[get_encryption_algorithm]())
IF @encryption_algorithm IS NULL
BEGIN
RAISERROR(27156, 16, 1, 'ENCRYPTION_ALGORITHM') WITH NOWAIT
END
SET @key_name = 'MS_Enckey_Env_'+CONVERT(varchar,@new_environment_id)
SET @certificate_name = 'MS_Cert_Env_'+CONVERT(varchar,@new_environment_id)
SET @sqlString = 'CREATE CERTIFICATE ' + @certificate_name + ' WITH SUBJECT = ''ISServerCertificate'''
IF NOT EXISTS (SELECT [name] FROM [sys].[certificates] WHERE [name] = @certificate_name)
EXECUTE sp_executesql @sqlString
SET @sqlString = 'CREATE SYMMETRIC KEY ' + @key_name +' WITH ALGORITHM = '
+ @encryption_algorithm + ' ENCRYPTION BY CERTIFICATE ' + @certificate_name
IF NOT EXISTS (SELECT [name] FROM [sys].[symmetric_keys] WHERE [name] = @key_name)
EXECUTE sp_executesql @sqlString
DECLARE @retval int
EXECUTE AS CALLER
EXEC @retval = [internal].[init_object_permissions] 3, @new_environment_id, @caller_id
REVERT
IF @retval <> 0
BEGIN
RAISERROR(27153, 16, 1) WITH NOWAIT
END
IF @tran_count = 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @tran_count = 0
ROLLBACK TRANSACTION;
ELSE IF XACT_STATE() <> -1
ROLLBACK TRANSACTION @savepoint_name;
THROW
END CATCH
RETURN 0
GO
Download as SQL file
NOTE: Please use at own risk and let me know it things could be improved!
