Archive DTS Packages

  • How can I archive off DTS packages before Sunsetting and Old SQL 2000 Server?

    I have only seen articles about migrating them to SSIS but that is not what I have been asked to do.

    Any help is greatly appreciated.

  • presuming they are held in msdb, copy them off to the file system. this script will do that for all DTS packages if there are a lot, else it can be done manually.

    if exists (select 1 from sysobjects where name = 'usp_backupDTS' and type = 'P')

    drop proc usp_backupDTS

    go

    CREATE PROC dbo.usp_backupDTS

    /***********************************************************************************

    usp_backupDTS

    Creates a set of SQL statements, each of which will backup one package to a

    structured storage file (.DTS) in a special backup directory for DR purposes..

    -----------------------------------------------------------------------------------

    CHANGE HISTORY

    -----------------------------------------------------------------------------------

    DATE WHO COMMENT

    ---------- ----------- ------------------------------------------------------------

    27/02/2007 Carl Start.

    ***********************************************************************************/

    AS

    SET NOCOUNT ON

    DECLARE @TARGETDIR varchar(1000), @SQL varchar(300)

    CREATE TABLE #SQL (SQLStatement varchar(300))

    SET @TARGETDIR = 'directory of your choice'

    INSERT INTO #SQL

    SELECT distinct

    'exec master.dbo.xp_cmdshell ' + '''DTSRUN.EXE /S '

    + CONVERT(varchar(200), SERVERPROPERTY('servername'))

    + ' /E '

    + ' /N '

    + '"' + name + '"'

    + ' /F '

    + '"' + @TARGETDIR + name + '.dts"'

    + ' /!X' + ''''

    FROM msdb.dbo.sysdtspackages P

    select * from #SQL

    -----------------------------------------------------------------------------------

    -- Initialize

    -----------------------------------------------------------------------------------

    DECLARE Command CURSOR FOR

    SELECT SQLStatement

    FROM #SQL

    OPEN Command

    FETCH NEXT FROM Command INTO @SQL

    -----------------------------------------------------------------------------------

    -- Cursor Loop Start

    -----------------------------------------------------------------------------------

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --print @SQL

    execute (@SQL)

    FETCH NEXT FROM Command INTO @SQL

    END

    -----------------------------------------------------------------------------------

    -- Cursor Loop End

    -----------------------------------------------------------------------------------

    close Command

    deallocate Command

    -----------------------------------------------------------------------------------

    -- Finalize

    -----------------------------------------------------------------------------------

    drop table #SQL

    grant all on dbo.usp_backupDTS to public

    GO

    ---------------------------------------------------------------------

  • Thank you, I will try this.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply