Move SSIS Packages Between Servers

create procedure dbo.move_ssis_package
    @from_server_name varchar(256),
    @to_server_name varchar(256),
    @package_name sysname
as
begin
    set nocount on;

    declare @sql_command nvarchar(4000),
            @folder_id uniqueidentifier,
            @foldername sysname;

    select @sql_command = N'
            SELECT @folder_id = pf2.[folderid]
            FROM ['+@from_server_name+'].[msdb].[dbo].[sysdtspackagefolders90] pf
            INNER JOIN ['+@from_server_name+'].[msdb].[dbo].[sysdtspackages90] p
            ON pf.folderid = p.folderid
            LEFT OUTER JOIN ['+@to_server_name+'].[msdb].[dbo].[sysdtspackagefolders90] pf2
            ON pf.[foldername] = pf2.[foldername]
            WHERE p.name = @package_name';

    exec sp_executesql
        @sql_command,
        N'@package_name sysname, @folder_id uniqueidentifier OUTPUT',
        @package_name = @package_name,
        @folder_id = @folder_id output;

    if @@ROWCOUNT > 1
        begin
            raiserror('This package exists in more than one location.', 16, 1);
        end;

    if @folder_id is null
        begin
            raiserror('SSIS Folder does not exist.', 16, 1);
        end;

    select @sql_command = 'DELETE ['+@to_server_name+'].[msdb].[dbo].[sysdtspackages90] WHERE name = @package_name';

    exec sp_executesql
        @sql_command,
        N'@package_name sysname',
        @package_name = @package_name;

    select @sql_command = N'
        INSERT ['+@to_server_name+'].[msdb].[dbo].[sysdtspackages90]
            SELECT [name]
            ,[id]
            ,[description]
            ,[createdate]
            ,@folder_id AS [folderid]
            ,[ownersid]
            ,[packagedata]
            ,[packageformat]
            ,[packagetype]
            ,[vermajor]
            ,[verminor]
            ,[verbuild]
            ,[vercomments]
            ,[verid]
            ,[isencrypted]
            ,[readrolesid]
            ,[writerolesid]
            FROM ['+@from_server_name+'].[msdb].[dbo].[sysdtspackages90]
            WHERE name = @package_name';

    exec sp_executesql
        @sql_command,
        N'@package_name sysname, @folder_id uniqueidentifier',
        @package_name = @package_name,
        @folder_id = @folder_id;
end;