DTS development export

• SELMI

  Newbie

  Points: 1

  More actions

  November 9, 2004 at 2:38 am

  #60134

  Hello,

  For deployment i need to have all my DTS developments and databases.

  I'm not sure that i have to backup the msdb database to export all DTS developments.

  Thanks for your help.

   

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  November 9, 2004 at 11:02 am

  #529491

  I'm not sure I understand what your question is.  Are you asking how to deploy DTS packages to another server?

  If that's what you want, you can either backup backup msdb and restore on the other server (don't do this if you're moving packages to a production server that already has packages and jobs) or open each package and save to the other server.

  Greg

  Greg

• Mark Schneeberger

  SSC Veteran

  Points: 273

  More actions

  November 10, 2004 at 2:07 am

  #529567

  See the script below to quickly save all your DTS packages. Note that packages will be appended to each other if the batch file is executed more than once and the old packages have not been removed.

  -- /E : Use a trusted connection

  -- /!X : Do not execute the DTS package

  -- DTSRUN.EXE /S ServerName /E /N PackageName /F FileName /!X

  -- Copy the results and add to a batch file for executing

  DECLARE @TARGETDIR varchar(1000)

  SET  @TARGETDIR = 'F:\Uploads\'

  SELECT distinct 

   'DTSRUN.EXE /S '

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

   + ' /E '

   + ' /N '

   + '"' + name  + '"'

   + ' /F '

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

   + ' /!X'

  FROM msdb.dbo.sysdtspackages P

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  November 10, 2004 at 4:06 pm

  #529698

  Nice script, rubbercow.  I tried it this morning and it worked great.  A good way to write a bunch of local packages to files.

  Thanks,

  Greg

  Greg