Delete old versions of DTS packages

if exists (select * from sysobjects 
			where id = object_id('dbo.usp_DelOldDTSPkgVersions') 
				and sysstat & 0xf = 4)
	drop procedure dbo.usp_DelOldDTSPkgVersions
GO

CREATE PROCEDURE usp_DelOldDTSPkgVersions 
/*************************************************************************
SQL SERVER OBJECT NAME: 
	dbo.usp_DelOldDTSPkgVersions
PURPOSE:
	Old versions of DTS packages from msdb database
ACTIONS:
	Accept package name parameter 
	Create cursor with old versions of packages matching passed package name
	For each record in cursor
		Execute sp_dump_dtspackagelog to remove package logs
			NOTE: removal of package log record is supposed to 
			cascade delete step log and task log records
		Execute sp_drop_dtspackage to remove old version
INPUTS:
	@vcrPkgName - sysname  - Optional parameter to filter by package name
OUTPUTS:
	@intErr via RETURN - non-zero value indicates failure
	Records removed from 
		msdb.dbo.sysdtspackages
		msdb.dbo.sysdtspackagelog
		msdb.dbo.dbo.sysdtssteplog
		msdb.dbo.dbo.sysdtstasklog
USAGE:
	EXEC usp_DelOldDTSPkgVersions @vcrPkgName = '<Package Name Filter>'

	EXAMPLE: EXEC usp_DelOldDTSPkgVersions @vcrPkgName = 'Devel'
		This will delete old versions for packages that have 'Devel' in the name

MODIFICATION HISTORY
	28-Nov-2002 Phillip Carter - Initial Development

*************************************************************************
Thanks to Antares686 at qa.sqlservercentral.com for 
the select statement in the cursor
*************************************************************************/
	-- PASSED PARAMETERS
	@vcrPkgName sysname = NULL
AS

BEGIN -- end procedure
	SET NOCOUNT ON

	-- declare local variables
	DECLARE @vcrName sysname -- package name
	DECLARE @unqID uniqueidentifier -- GUID for package id
	DECLARE @unqVerID uniqueidentifier -- GUID for package version
	DECLARE @vcrMsg varchar(255) -- message string
	DECLARE @intRC int -- return code from stored procedures
	DECLARE @intErr int -- indicates error in individual operation
	DECLARE @intFail int -- indicates overall failure

	-- init local variables
	SET @vcrName = ''
	SET @unqID = NULL
	SET @unqVerID = NULL
	SET @vcrMsg = ''
	SET @intRC = 0
	SET @intErr = 0
	SET @intFail = 0
	-- add wildcard characters to package name variable
	SET @vcrPkgName = '%' + COALESCE(@vcrPkgName, '') + '%'
	
	-- declare cursor for all old versions of packages 
	-- matching passed package name variable
	DECLARE curVer CURSOR FAST_FORWARD FOR 
		SELECT name, id, versionid
		FROM msdb.dbo.sysdtspackages 
		WHERE versionid in (
			SELECT versionid 
			FROM msdb.dbo.sysdtspackages sPkg
				INNER JOIN (
						SELECT id as MaxID 
							, MAX(createdate) as MaxCrDt
						FROM msdb.dbo.sysdtspackages 
						GROUP BY id
					) as MaxCD
				ON sPkg.id = MaxCD.MaxID 
					AND sPkg.createdate <> MaxCD.MaxCrDt
			)
			AND name LIKE @vcrPkgName
	
	OPEN curVer
	
	FETCH NEXT FROM curVer INTO @vcrName, @unqID, @unqVerID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Remove package logs for old version
		-- NOTE: removal of package log record is supposed to 
		-- cascade delete step log and task log records
		EXEC @intRC = msdb.dbo.sp_dump_dtspackagelog 
			@name = @vcrName, @versionid = @unqVerID
	
		-- capture system error variable
		SELECT @intErr = @@ERROR
	
		-- check for error condition
		IF @intRC = 0 AND @intErr = 0
		BEGIN 
			-- print success message
			SELECT @vcrMsg = 'Removed log records for package ' + @vcrName 
			SELECT @vcrMsg = @vcrMsg + ', version ' + CAST(@unqVerID as varchar(128))
			PRINT @vcrMsg
	
			-- remove old version of the package
			EXEC @intRC = msdb.dbo.sp_drop_dtspackage 
				@name = @vcrName, @id = @unqID, @versionid = @unqVerID
			-- capture system error variable
			SELECT @intErr = @@ERROR
	
			-- check for error condition
			IF @intRC = 0 AND @intErr = 0
			BEGIN
				-- print success message
				SELECT @vcrMsg = 'Removed version ' + CAST(@unqVerID as varchar(128)) 
				SELECT @vcrMsg = @vcrMsg + ' for package ' + @vcrName
				PRINT @vcrMsg
			END
			ELSE
			BEGIN
				-- print error message
				SELECT @vcrMsg = 'ERROR: Removing Version ' + CAST(@unqVerID as varchar(128)) 
				SELECT @vcrMsg = @vcrMsg + ' for package ' + @vcrName
				PRINT @vcrMsg
				SELECT @vcrMsg = 'ERROR: ' + CAST(@intErr as varchar(10)) 
				SELECT @vcrMsg = @vcrMsg + ', SP Return: ' + CAST(@intRC as varchar(10)) 
				PRINT @vcrMsg
				-- set intFail and reset intRC and intErr
				SELECT @intFail = 1, @intErr = 0, @intRC = 0
			END
		END
		ELSE
		BEGIN
			-- print error message
			SELECT @vcrMsg = 'ERROR: Removing log records for package ' + @vcrName 
			SELECT @vcrMsg = @vcrMsg + ', version ' + CAST(@unqVerID as varchar(128))
			PRINT @vcrMsg
			SELECT @vcrMsg = 'ERROR: ' + CAST(@intErr as varchar(10)) 
			SELECT @vcrMsg = @vcrMsg + ', SP Return: ' + CAST(@intRC as varchar(10)) 
			PRINT @vcrMsg
			-- set intFail and reset intRC and intErr
			SELECT @intFail = 1, @intErr = 0, @intRC = 0
		END
	
		-- fetch next set of values from cursor
		FETCH NEXT FROM curVer INTO @vcrName, @unqID, @unqVerID
	
	END
	
	-- close and deallocate cursor
	CLOSE curVer
	DEALLOCATE curVer

	IF @intFail <> 0
	BEGIN
		-- print error message
		PRINT REPLICATE('*', 60)
		PRINT 'Operation encounter Errors. Check previous messages.'
	END

	RETURN (@intFail)

END	-- end procedure

GO