How do I Copy back in a DTS script?

[David Greenberg]

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 ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"David Greenberg" <da*****@iba.or g.il> wrote in message
news:40******** *************** @news.frii.net. ..

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 ***
Don't just participate in USENET...get rewarded for it!

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
LoadFromSQLServ er method followed by the SaveToStorageFi le 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

[Dan Guzman]

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("S cripting.FileSy stemObject")
Set DtsPackage = CreateObject("D TS.Package2")

'get package
DtsPackage.Load FromSqlServer
SqlServerName, , , 256, , , , DtsPackageName

'generate file name
DtsPackageFileN ame = "C:\MyDtsPackag es\" & _
DtsPackage.Name & ".dts"

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

'create structured storage file
DtsPackage.Save ToStorageFileAs _
DtsPackage.Name , _
DtsPackageFileN ame
--
Hope this helps.

Dan Guzman
SQL Server MVP

"David Greenberg" <da*****@iba.or g.il> wrote in message
news:40******** *************** @news.frii.net. ..

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 ***
Don't just participate in USENET...get rewarded for it!