Monday, 17 December 2012

SSIS 2012 Copy Environments

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!

2 comments:

  1. Perhaps you could also copy all references to the packages.

    ReplyDelete
    Replies
    1. It now also adds the reference to the project.

      Delete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...