SQL Server – Save all DTS Packages

Here’s another useful proc I found to back up all your DTS packages (from I can’t remember where).  Put this in your master database: 


Create procedure usp_SavePackages
@Path    
varchar(128
)
as
/*

*/

    
set nocount on

declare 
@objPackage 
int
declare 
@PackageName varchar(128
)
declare @rc 
int
declare 
@ServerName varchar(128
)
declare @FileName varchar(128
)
declare    @FilePath varchar(128
)
declare    @cmd varchar(2000
)
    
    
select     @ServerName 
@@ServerName ,
        @FilePath 
@Path
    
    
if right(@Path,1) <> 
‘\’
    begin
        select @Path = @Path + ‘
\

    end
    
    — create output directory – will fail if already exists but …
    select    @cmd = ‘
mkdir 
‘ + @FilePath
    exec master..xp_cmdshell @cmd
    
    
create table #packages (PackageName varchar(128))
    insert     #packages
        (PackageName)
    select     distinct name
    from    msdb..sysdtspackages
    
    select    @PackageName = ”
    while @PackageName < (select max(PackageName) from #packages)
    begin
        select    @PackageName = min(PackageName) from #packages where PackageName > @PackageName

        select    @FileName = @FilePath + @PackageName + ‘
.dts


        exec @rc = sp_OACreate ‘
DTS.Package
‘, @objPackage output
        if @rc <> 0
        begin
            raiserror(‘
failed to create package rc %d
‘, 16, -1, @rc)
            return
        end

        exec @rc = sp_OAMethod @objPackage, ‘
LoadFromSQLServer
‘ , null,
            @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName
        if @rc <> 0
        begin
            raiserror(‘
failed to load package rc %d, package %s
‘, 16, -1, @rc, @PackageName)
            return
        end
        
        — delete old file
        select @cmd = ‘
del 
‘ + @FileName
        exec master..xp_cmdshell @cmd, no_output
        
        exec @rc = sp_OAMethod @objPackage, ‘
SaveToStorageFile
‘, null, @FileName
        if @rc <> 0
        begin
            raiserror(‘
failed to save package rc %d, package %s
‘, 16, -1, @rc, @PackageName)
            return
        end
        
        exec @rc = sp_OADestroy @objPackage
    end

GO


Colorized by: CarlosAg.CodeColorizer