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), SELECT @sql_command = 'SELECT @folder_id = pf2.[folderid] FROM [' + @from_server_name + '].[msdb].[dbo].[sysdtspackagefolders90] pf 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] EXEC sp_executesql @sql_command, N'@package_name sysname', @package_name = @package_name SELECT @sql_command = 'INSERT [' + @to_server_name + '].[msdb].[dbo].[sysdtspackages90] 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.
The post A Stored Procedure to Move SSIS Packages Between Servers first appeared on Adventures In SQL.
