A Stored Procedure to Move SSIS Packages Between Servers

Today’s post is one that I have been debating on whether to publish for a while. The purpose of the stored procedure I am sharing is to move SSIS packages stored via SQL Server Storage from one server SQL 2005 server to another in a way that can easily be invoked by any release management system that can call stored procedures. The part I have reservations about is that it uses linked servers. I almost never allow linked servers to be created on the servers I manage, mostly because they can be a security problem. Breaking the rules in this case is what was right for the particular problems I was trying to solve. Please consider whether you can implement this logic another way before using this stored procedure in your environment.

This stored procedure is not terribly complicated so I will run through what it does fairly quickly. The first step is to get the folder_id of the package we want to copy. If it gets more than 1 folder name back it throws an error because it does not know which package to move. If the folder_id returned is null then an error is thrown. If the stored procedure makes it through those checks, the current version a the destination is deleted and the new version is copied there.

Here is the code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CREATE PROCEDURE dbo.move_ssis_package @from_server_name varchar(256), @to_server_name varchar(256), @package_name sysname

AS

DECLARE @sql_command nvarchar(4000),
@folder_id uniqueidentifier,
@foldername sysname

SELECT @sql_command = '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 = '
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

Please let me know if you run into any issues, have any ideas that would make this stored procedure better or just want to share how you are using it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

3 thoughts on “A Stored Procedure to Move SSIS Packages Between Servers

  1. I use my own version (change de collation if necessary) that works even when a package is in more than one folder whenever you create de folder in destination
    USE [msdb]
    GO
    /****** Object: StoredProcedure [dbo].[move_ssis_packageiam] Script Date: 05/22/2014 12:55:27 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[move_ssis_packageiam] @from_server_name varchar(256), @to_server_name varchar(256),
    @folder_name_delpaquete varchar(100),
    @folder_id_delpaquete varchar(100),
    @package_name sysname

    AS

    DECLARE @sql_command nvarchar(4000),
    @folder_id uniqueidentifier,
    @foldername sysname

    SELECT @sql_command = ‘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] collate Modern_Spanish_CI_AS = pf2.[foldername]
    WHERE p.name = @package_name and p.folderid=cast (”’+@folder_id_delpaquete+ ”’ as uniqueidentifier ) and pf2.foldername=”’+@folder_name_delpaquete+””
    –print @sql_command
    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 and folderid= cast (”’+cast(@folder_id as varchar(100))+ ”’ as uniqueidentifier )’
    –print @sql_command
    EXEC sp_executesql @sql_command, N’@package_name sysname’, @package_name = @package_name

    SELECT @sql_command = ‘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 and folderid= cast (”’+@folder_id_delpaquete+ ”’ as uniqueidentifier )’

    print @package_name
    print @folder_id
    EXEC sp_executesql @sql_command, N’@package_name sysname, @folder_id uniqueidentifier’, @package_name = @package_name, @folder_id=@folder_id

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.