Rudy Rodarte . com

Information

This article was written on 11 Feb 2010, and is filled under SQL Blogs.

Current post is tagged

, , ,

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

Rudy Rodarte is a SQL Server Database professional based in Austin, TX. Over his career, Rudy has worked with SSRS, SSIS, performance tuning troubleshooting. When away from Keyboard, Rudy goes to Spurs and Baylor Bear sporting events.

More Posts - Website - Twitter