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.
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
Msg 102, Level 15, State 1, Procedure move_ssis_package, Line 19
Incorrect syntax near ‘1’.