[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Save DTS Packages

Posted on 2004-11-25
6
Medium Priority
?
460 Views
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?
0
Comment
Question by:platinumbay
  • 3
  • 2
6 Comments
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 1400 total points
ID: 12676750
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. http://www.sqldts.com/default.aspx?242
0
 
LVL 6

Assisted Solution

by:mcp111
mcp111 earned 600 total points
ID: 12678046
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\" + objpkginfo.name + ".dts"
        'save to dts file
        objPkgInfo.SaveToStorageFile filename
         Set objPkgInfo = colPkgInfo.Next
    Loop

end sub()
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12678122
Here is another way to do it with the DTSRUN comand line and a query in SQL QA, no external program needed.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:MikeWalsh
ID: 12678126
0
 
LVL 6

Author Comment

by:platinumbay
ID: 12678243
mcp111:

I got your code working except for one line:
objPkgInfo.SaveToStorageFile(filename)

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

Author Comment

by:platinumbay
ID: 12678833
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)
            pkg.SaveToStorageFile(FileName)
        Next
    End Sub
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 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