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 www.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