Save DTS Packages

Posted on 2004-11-25
Last Modified: 2013-11-30
Is there a way to automate the export of DTS PAckages to a *.DTS file, rather than opening each one manually?
Question by:platinumbay
    LVL 13

    Accepted Solution

    I don't believe there is an easy way to automate this job from within SQL. There is a tool out there for free called DTS Backup. I have used this tool before and it makes a nice job of saving DTS packages to a .dts file.
    LVL 6

    Assisted Solution

    Here's how you can do this using vb

    sub savepackages()

    Dim objDTSAppl      As DTS.Application
        Dim objPkgSQLServer As DTS.PackageSQLServer
        Dim colPkgInfo      As DTS.PackageInfos
        Dim objPkgInfo      As DTS.PackageInfo
        Dim strMsg          As String
        Set objDTSAppl = New DTS.Application
        Set objPkgSQLServer = objDTSAppl.GetPackageSQLServer( _
            "(local)", "sa", "", DTSSQLStgFlag_Default)
        Set colPkgInfo = objPkgSQLServer.EnumPackageInfos("", False, "")
           Set objPkgInfo = colPkgInfo.Next
        Do Until colPkgInfo.EOF
            dim filename as string
            'create filename for each package
            filename="c:\packages\" + + ".dts"
            'save to dts file
            objPkgInfo.SaveToStorageFile filename
             Set objPkgInfo = colPkgInfo.Next

    end sub()
    LVL 13

    Expert Comment

    Here is another way to do it with the DTSRUN comand line and a query in SQL QA, no external program needed.
    LVL 13

    Expert Comment

    LVL 6

    Author Comment


    I got your code working except for one line:

    Says the method is not a member of DTS.PackageInfo.  I am using the v2.0 DTS interop.
    LVL 6

    Author Comment

    The DTS BAckup tool worked great, thanks!

    Thanks also for the code, I will be using it in the future.  I got it to work as well:

        Sub ExportPackages()
            Dim app As New DTS.Application
            Dim FileName As String
            Dim s As DTS.Steps
            Dim package As DTS.PackageSQLServer
            Dim infos As DTS.PackageInfos

            package = app.GetPackageSQLServer(SQLServer, SQLUser, SQLPass, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default)
            infos = package.EnumPackageInfos("", True, "")

            For Each info As DTS.PackageInfo In infos
                If infos.EOF Then Exit For

                Dim pkg As New DTS.Package
                FileName = "d:\sqldata\dtspackages\" + pkg.Name + ".dts"
                pkg.LoadFromSQLServer(SQLServer, SQLUser, SQLPass, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, Nothing, Nothing, Nothing, info.Name, Nothing)
        End Sub

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now