Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Archive DTS Packages Expand / Collapse
Author
Message
Posted Monday, November 25, 2013 4:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:48 PM
Points: 32, Visits: 306
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.
Post #1517491
Posted Tuesday, November 26, 2013 6:45 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:21 AM
Points: 5,886, Visits: 13,042
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



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

Post #1517656
Posted Tuesday, November 26, 2013 8:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:48 PM
Points: 32, Visits: 306
Thank you, I will try this.
Post #1517718
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse