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' |
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 GODownload as SQL file
NOTE: Please use at own risk and let me know it things could be improved!
Perhaps you could also copy all references to the packages.
ReplyDeleteIt now also adds the reference to the project.
Delete