DTS Structured Storage File 3

[dbomrrsm]

Someone has deleted a DTS package from a server. I have a saved version which was saved as a structured storage file with file extension .dts.

Is there any way of using this file to recreate the DTS package in EM.

DBomrrsm

 

[JayKusch]

how about ...

DTSBackup.exe /R /S MY_Server /p C:\DTSBackup\Files\ /U sa /P Abc123 /C

This will restore all file packages from the location specified, to the server as (server packages), using SQL Server Security.

Here are the command switches:

/B (Backup) : This starts a Backup operation (This switch has no parameters).

/R (Restore) : This starts a Restore operation (This switch has no parameters).

/S server_name : The name of your SQL Server.

/p path_directory_name : The path to place the COM files or restore from.

/s Storage_Type : S = Server, R = Repository. Leave switch out to backup both. Required for restore.

/U Username : Leave switch out to use NT Integrated security.

/P Password : As above.

/C (Command Line) : Activates Command Line mode (This switch has no parameters).


Thanks

J. Kusch

 

[dbomrrsm]

J. Kusch

That looks just great - one problem when I run this (i take it it is run from a DOS command (run CMD) it returns a message:

'DTSBackup.exe' is not recognized as an internal or external command, operable program or batch file.

Any clues.

Thanks

DBomrrsm

 

[JayKusch]

Terribly sorry about that one! Had that snippette saved away on a doc and did not have reference to the VB app that was behind the whole process. Here is the link I pulled the original info from.

http://www.databasejournal.com/features/mssql/article.php/1462601

Thanks

J. Kusch

 

[dbomrrsm]

Cheers

[blue]DBomrrsm[/blue]

 

[nigelrivett]

See

http://www.nigelrivett.net/s_LoadPackageToServer.html

It will load a structured storage file and save it to msdb.
Combine it with

http://www.nigelrivett.net/s_ProcessAllFilesInDir.html

to load all files from a directory.

If you just have one file though you may as well use enterprise manager.
right click on data transformation services, open package
open the package then package, save as, select sql server as location.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

Note that the formatting will be lost if you us the SP - but not using enterprise manager depending on how it was saved.
I always prefer to work with dts packages from storage file anyway - makes them easier to release.

Have a look at

http://www.nigelrivett.net/SaveAllDTSPackagesToFiles.html

which will save all the dts packages to files (for backup?).
And

http://www.nigelrivett.net/ScriptDTSProperties.html

which scripts the properties of all dts packages - useful for saving in sourcesafe to detect changes and for searching.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.