Hi !
I know how to save a DTS as a structured storage file and how to
recreate a DTS using that file.
What I can't find is a command, either in t-sql or from a DOS level,
with which I can save the DTS as a structured storage file, for example
in a scheduled job for backup purposes.

Hopefully some out there has an answer !!!

Thanks
David Greenberg


*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]

If I understand you correctly, probably the easiest way to do this is with
the SQLDMO object model - you can use the Package2 object's
LoadFromSQLServer method followed by the SaveToStorageFile method to do what
you want. So you could write a script in VBScript, Perl etc., or perhaps
look at the sp_OA% procedures if you need to do this from TSQL (although
there are security issues for non-sysadmins using those procs).

Also make sure you are backing up msdb, which is where DTS packages stored
in SQL Server are located - that might be enough, depending on your
requirements.

Simon

To add to Simon's response, below is a VBScript code snippet that will copy
a package from a SQL Server repository to a structured storage file.

SqlServerName = "MyServer"
DtsPackageName = "MyPackage"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set DtsPackage = CreateObject("DTS.Package2")

'get package
DtsPackage.LoadFromSqlServer
SqlServerName, , , 256, , , , DtsPackageName

'generate file name
DtsPackageFileName = "C:\MyDtsPackages\" & _
DtsPackage.Name & ".dts"

'delete file if it already exists
If Fso.FileExists(DtsPackageFileName) Then
Fso.DeleteFile DtsPackageFileName
End If

'create structured storage file
DtsPackage.SaveToStorageFileAs _
DtsPackage.Name, _
DtsPackageFileName


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]