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
GO
Download as SQL fileNOTE: 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