SQL Server – Save all DTS Packages – Codebureau

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