Delete old versions of DTS packages

philcart, 2002-11-30

This script will remove old versions of DTS packages. It accepts a single parameter which is used as a filter criteria on the package name. It will also remove package log records for the version that is deleted.
EXAMPLE: EXEC usp_DelOldDTSPkgVersions @vcrPkgName = 'Devel'
This will delete old versions for packages that have 'Devel' in the name

Questions and comments welcome.

    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

Rate

5 (2)

Share

Share

Rate

5 (2)

Related content

Run a Script Against All User Databases

Now you can run administrative T-SQL tasks against all your databases in one shot. Very usefull for environments that have a seperate database for each client, ASP's, etc. It's pretty straight forward and can be used for almost any task. This example Looks for a table and prints the database name and selects from the […]

danw

2001-10-21

3,029 reads

Find Longstanding Open Transactions

Ever forget to commit a transaction and then find out hours later that there is deadlocked transactions all over your database server? Worse yet has one of your coworkers done this to your database server? Never again, this stored procedure will net send the machine that has an open transaction, and send you (The DBA) […]

4 (1)

danw

2001-10-08

8,733 reads