--=====================================================================
-- Author:		Yan Pan
-- Description:	This stored procedure is used to generate a script 
--	for all the SSIS package stored in a SQL Server 
--	that are protected by both database roles and a common password.
--	The output script can be executed on another server 
--	to copy SSIS packages from the source server.
--=====================================================================

USE msdb

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].copySSISpackages_encryptedByPwd') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.copySSISpackages_encryptedByPwd
GO

CREATE PROCEDURE dbo.copySSISpackages_encryptedByPwd
	@srcServer sysname, -- Source server name
	@destServer sysname, -- Destination server name
	@oldPassword varchar(20), -- The password used to encrypt SSIS packages on the source server
	@newPassword varchar(20), -- The password used to encrypt SSIS packages on the destination server
	@srcUser sysname = '', -- SQL Server login used to connect to the source server
	@srcPassword sysname = '', -- Password of the SQL Server login on the source server
	@destUser sysname = '', -- SQL Server login used to connect to the destination server
	@destPassword sysname = '' -- Password of the SQL Server login on the destination server
AS
BEGIN
	SET NOCOUNT ON;

	print 'USE msdb'

	-- Copy SSIS packages
	select 'EXEC [master].[sys].[xp_cmdshell] ''dtutil /Quiet /COPY SQL;' + 
	case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end 
	+ ' /SQL ' + case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end
	+ ' /SOURCESERVER ' + @srcServer 
	+ case @srcUser when '' then '' else ' /SourceUser ' + @srcUser + ' /SourcePassword ' + @srcPassword end
	+ ' /DESTSERVER ' + @destServer
	+ case @destUser when '' then '' else ' /DestUser ' + @destUser + ' /DestPassword ' + @destPassword end 
	+ ' /Decrypt ' + @oldPassword + ''''
	from msdb.dbo.sysdtspackages90 pkg join msdb.dbo.sysdtspackagefolders90 fld
	on pkg.folderid = fld.folderid

	-- Change the encryption password from @oldPassword to @newPassword on the destination server
	select 'EXEC [master].[sys].[xp_cmdshell] ''dtutil /Quiet /SQL ' + 
	case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end 
	+ ' /SOURCESERVER ' + @destServer 
	+ ' /DESTSERVER ' + @destServer + ' /Decrypt ' + @oldPassword + ' /Encrypt SQL;' + 
	case foldername when '' then '"' + [name] + '"' else '"' + foldername + '\' +  [name] + '"' end
	+ ';3;' + @newPassword + ''''
	from msdb.dbo.sysdtspackages90 pkg join msdb.dbo.sysdtspackagefolders90 fld
	on pkg.folderid = fld.folderid

	-- Copy SSIS package roles
	select 'EXEC [dbo].[sp_dts_setpackageroles] @name = ''' + pkg.[name] + ''', '
	+ '@folderid = ''' + CONVERT(CHAR(36), folderid) + ''', @readrole = ' + isNull('''' + p1.[name] + '''', 'NULL') + ',  '
    + '@writerole = ' + isNull('''' + p2.[name] + '''', 'NULL') 
	FROM msdb.dbo.sysdtspackages90 pkg 
	left join sys.database_principals p1 on p1.sid = pkg.readrolesid and p1.[type] = 'R'
	left join sys.database_principals p2 on p2.sid = pkg.writerolesid and p2.[type] = 'R' 
END
GO