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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
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.
