Deploy SSIS Packages across servers

Background
Recently I had to deploy a set of SSIS packages stored in the msdb of a development server into a test server. I should mention that a SSIS package could be stored in the file system, in a package store or in the msdb database of a SQL Server instance.
I don’t want to discuss the reasons to choose a specific option but if you’re curious I left here a couple of articles:
Deployment Storage Options for SSIS Package
What are the advantages/disadvantages of storing SSIS packages to MSDB vs File System?

To organize my packages I’ve created a root folder with the name of the project, and three subfolders according to my task categories:

The Task
The task to perform is to move all of the SSIS packages stored in this folder to a test server with similar settings (SQL server Database running on Windows Server 2008 R2 Enterprise)

Discussion
If I used the file system or package store storage option I just need to move the packages to the new location, but this is not the case. A simple option is to use the SSMS (management studio) and individually export each package:

But I’m too lazy to do that and I want to move all of the packages at the same time.

There is a deployment utility for SSIS projects in the Business Intelligence Development Studio (BIDS). Here is a great article which explains its usage (also apply for SQL Server 2008):
Deploying SSIS Packages in SQL Server 2005

The disadvantage of this “just click next up to the end” option is you can choose one and only one target folder. At the moment I couldn’t figure out a way to have logical folders in the BIDS solution explorer under the SSIS Packages folder and map them to their correspondents target folder in the msdb.

Another option is to use the PowerShell extension for SSIS:
http://sev17.com/2011/02/02/importing-and-exporting-ssis-packages-using-powershell/
The main drawback of this option is these extensions need to be installed, and the execution of scripts must be enabled, but it seems to work nicely.
My last option in discussion, and the one that I finally used is the DTUTIL command line utility, but like I said before, I’m too lazy so I’ve created a couple of stored procedures that I want to share with you.

createSSISpackagesFolder
This stored procedure creates a folder in the root folder of the msdb database. The input parameter is the name of the folder. The name of the folder must be enclosed into single quotes if contains special characters. Please feel free to modify this stored procedure to create subfolders; you will need to add another input parameter and lookup the parent folder id.

USE [msdb]
GO

/****** Object: StoredProcedure [dbo].[createSSISpackagesFolder] Script Date: 04/02/2013 15:49:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[createSSISpackagesFolder]
@destFolder sysname = '' -- Specify the name of the folder in the msdb
AS
BEGIN
SET NOCOUNT ON;

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

DECLARE @folders table(folderid uniqueidentifier, parentfolderid uniqueidentifier, foldername sysname);
insert into @folders
EXEC msdb.dbo.sp_ssis_listfolders '00000000-0000-0000-0000-000000000000';

if (select Count(*) from @folders where foldername = @destFolder)=0
begin
/*Add a Folder*/

exec msdb.dbo.sp_ssis_addfolder

@parentfolderid = '00000000-0000-0000-0000-000000000000'

,@name = @destFolder;
end

EXEC msdb.dbo.sp_ssis_listfolders '00000000-0000-0000-0000-000000000000';

END

GO


copySSISpackagesPlus
This is the modified version of the stored procedure I’ve found in this article:
http://www.databasejournal.com/features/mssql/article.php/3734096/Using-dtutil-to-copy-SSIS-packages-stored-in-SQL-Server.htm
The procedure receives seven parameters: IP address or server name of the source and target server, SQL Server credentials if apply, that is, user name and password for each server (If Windows Authentication is used the credentials are not needed) and the name of the target folder. The stored procedure must be run from the source server. The name of the source folder and target folder must match. If you want another behavior it is easy to create another input parameter to have different source and target folder names.

USE [msdb]
GO

/****** Object: StoredProcedure [dbo].[copySSISpackagesPlus] Script Date: 04/02/2013 15:51:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[copySSISpackagesPlus]
@srcServer sysname='', -- Source server name
@destServer sysname='', -- Destination server name
@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
@srcFolder sysname = '' -- Specify the name of the source folder in the msdb
AS
BEGIN
SET NOCOUNT ON;

DECLARE @execStrings table(Idx int identity(1,1), execCmd varchar(1000))

Insert into @execStrings(execCmd)
select '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
from dbo.sysssispackages pkg join dbo.sysssispackagefolders fld
on pkg.folderid = fld.folderid
where fld.foldername = @srcFolder

DECLARE @cnt int = (select COUNT(*) from @execStrings);
DECLARE @tmpCmd varchar(1000);
WHILE(@cnt>0)
BEGIN
set @tmpCmd = (select execCmd from @execStrings where Idx = @cnt);
print @tmpCmd;
exec [master].[sys].[xp_cmdshell] @tmpCmd;
set @cnt = @cnt -1;
END

END

GO


Further questions or comments? Please write me.