Rudy Rodarte . com

Delete old DTS packages in SQL Server 2008

Do you have tons and tons of versions of DTS packages in SQL Server 2008/2005. Every little revision saves a new version on the server. You could end up with hundreds of revisions for just one package.

DTS packages

It is a pain to delete each of these one by one. The quickest way to delete these old packages is with a script. Rather than write a script, I went out and searched the web for a solution. I found the script I needed at SQL Server Central.
Delete old versions of DTS packages
here is the code:

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 = '
'
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

The code will delete all but the newest version of the DTS package. The only thing you need to supply is the DTS package name.

Rudy

, , ,