November 25, 2013 at 4:16 pm
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.
November 26, 2013 at 6:45 am
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
---------------------------------------------------------------------
November 26, 2013 at 8:30 am
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