Save DTS Packages

Is there a way to automate the export of DTS PAckages to a *.DTS file, rather than opening each one manually?
LVL 6
platinumbayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeWalshCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Partha MandayamTechnical DirectorCommented:
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
MikeWalshCommented:
Here is another way to do it with the DTSRUN comand line and a query in SQL QA, no external program needed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MikeWalshCommented:
0
platinumbayAuthor Commented:
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
platinumbayAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.