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 IF @folder_id IS NULL 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.
