Automating backups of DTS packages

  • Does anyone out there have or know of a way (other than SQL backups) to automate individually backing up DTS packages? I know how to do it manually saving it to a txt file but it would be nice if there was a script i could use to automate it for all of my packages. My scripting skills aren't up to par yet for me to write it myself. Any help is greatly appreciated.

    Michael Lampe


    Michael Lampe

  • How are you saving your DTS packages. If you are keeping them local to the server then they are stored in msdb. If msdb is backed up, so are they (along with SQL Agent job information since the SQL Agent tables are also in msdb). Here's a bit more on the locations and how to handle them:

    http://www.sqlservercentral.com/columnists/bknight/savingpackages.asp

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Thanks for your reply. I'm curently saving them locally and backing up msdb. The guy I'm working with on this wants it also saved as a COM-Structured file. The problem is having to manually going in and saving it. We have a lot of packages and many of them change frequently. I was hoping to automate it in some way.

    Michael Lampe


    Michael Lampe

  • Another way you can do it is to a DTS package file.

    Saw this in another thread last night and started to script it to generate and run against all my DTS packages.

    textcopy /S ServerName /U sa /P /D msdb /T sysdtspackages /C packagedata /W "where name='DTSName'" /F DTSText.dts /O

    This command will bring package out as dts file. Then it can be opened from EM/open package.

    My thought was use a cursor for the packages table to get all the names, then loop thru it altering the string above and submit using xp_cmdshell. This should easily be able to output all in a hurry.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply