What do all these "Save DTS Package" options mean?

Question

I'm creating a DTS package. After specifying the source, destination, and tables, I get the Save, schedule, and replicate package step, which has a Save DTS Package checkbox.

If I check the box to save, I get these options:

• SQL Server
• SQL Server Meta Data Services
• Structured Storage File
• Visual Basic File

Can anyone give a good explanation of what each option means, and the pros and cons of using it to save my DTS package? (Particularly if you have experience with the different options, and aren't just regurgitating something like this.)

Answer 1

These would be your primary options:

• SQL Server means in tables in the msdb database
• Structured Storage File means a .dts file

The file is portable and standalone. It can be run with dtsrun.exe and does not require a full install to run on an app server.

The msdb option means you need access to the msdb database but you gain backup/restore capability.

It depends on your setup and shop. I prefer the file option, but I'll use either as needed.

I would not use these:

• Visual Basic File: once saved you can't edit it again, only run it (and I forgot how now)
• SQL Server Meta Data Services: does it still apply?

Some more info on www.sqldts.com but it's not updated now. SSIS has mostly replaced DTS.