March 6, 2007 at 3:12 pm
Having created many DTS packages to import data into the database, I would like to ship these packages to remote sites where the same import packages can be used. How can I go about copying the packages to a distribution medium such as a CD or other files for shipping via FTP?
March 6, 2007 at 3:52 pm
here is an article:
March 7, 2007 at 5:51 am
if you do a search on 'DTS backups' you will find a script that will back each dts package up to a seperate structured storage file. (basically the script from article above out in a loop). Here it is:
f 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 = 'e:\server\data\mssql\dba_scripts\disrec\dts\'
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply