Moving DTS Packages - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Moving DTS Packages

Author

Topic

jpotucek
Constraint Violating Yak Guru

273 Posts

Posted - 2008-02-25 : 11:23:54

I need to move a DTS Package from one System to Another ... How is the best way to do this??????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 12:39:45

Refer this:-

http://www.sqldts.com/204.aspx

Haywood
Posting Yak Master

221 Posts

Posted - 2008-02-25 : 14:12:42

I've had better luck using TextCopy.exe and my own t-sql solution. This will iterate the sysdtspackages and export the last version to a Structured Storage File. The tool at sqldts doesn't preserve the gui layout when exporting, which for me wasn't a huge ordeal, but for the 'intended' users it was...

Thus the following hack was born...

[CODE]
/******************************************************************************
**
** Name: usp_Export_DTS_Packages_via_Textcopy.sql
**
** Description: Textcopy DTS packages from MSDB to Structured Storage File (*.dts).
** This preserves the formatting of the dts packages, unlike DTSBackup2000.
**
** Author: G. Rayburn
**
** Date: 09/19/2007
**
** Depends on: textcopy.exe in a valid server path.
** Standard login and password for use in the textcopy call.
** SQLAgent access to target fileshare.
**
** TODO: Errorhandling
** Add regex search/replace for single quotes, fwd/back
** slashes in output filename.
**
*******************************************************************************
** Modification History
*******************************************************************************
**
** Initial Creation: 09/19/2007 G. Rayburn
** 10/03/2007 G. Rayburn -- Added MAX(createdate) subquery.
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON

DECLARE @DynTextCopy varchar(4000)
, @PkgName sysname
, @FileName varchar(4000)
, @FilePath varchar(256)
, @UserName sysname
, @UserPassword sysname

SET @FilePath = '\\FooServer\BarShare' -- Dest. file path.
SET @UserName = 'user' --- Standard login.
SET @UserPassword = 'password' -- Password for login.

DECLARE curTextCopy CURSOR
READ_ONLY
FOR
SELECT [name]
FROM MSDB.dbo.sysdtspackages(NOLOCK)
GROUP BY [name]
ORDER BY [name]

OPEN curTextCopy

FETCH NEXT FROM curTextCopy INTO @PkgName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @DynTextCopy = 'textcopy /S ' + @@SERVERNAME + ' /U ' + @UserName + ' /P ' + @UserPassword + ' /D MSDB /T sysdtspackages /C packagedata /W "where name = ''' + @PkgName + ''' AND createdate = (SELECT MAX(createdate) FROM msdb.dbo.sysdtspackages (NOLOCK) WHERE [name] = ''' + @PkgName + ''')"' + ' /F "' + @FilePath + '\' + @PkgName + '.dts" /O'

PRINT @DynTextCopy
PRINT ''

EXEC master..xp_cmdshell @DynTextCopy

WAITFOR DELAY '00:00:05.000'
END
FETCH NEXT FROM curTextCopy INTO @PkgName
END

CLOSE curTextCopy
DEALLOCATE curTextCopy
GO
[/CODE]

Edit:

We've found that long-term, executing packages from Structured Files (*.dts) via fileshare was much more effective than saving the dts packages to the MSDB database and executing from there...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-25 : 15:22:59

>> I need to move a DTS Package from one System to Another

You can save it to target server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources