Saving a DTS package is easy, but where should you save it to. I'd have to

give the "it depends" answer, but I must. In SQL Server 2000, you can now save a

package in four different locations: locally on the SQL Server, in the Meta Data

Repository, as a COM-structured file or as a VB file. Most of these choices have

their perks and their purposes.  They also have their downfalls. This

article will cover these methods of saving a package and how to version control

your packages.

Local Packages

Saving packages locally is the easiest location to save a DTS package and is

the default method. Saving a package using this method will save the package

into some of the system tables in the msdb database (sysdtspackages among

others). What's nice about saving a package here is in a properly administered

database, the package will automatically be backed up in your regular system

database backup.

Packages here are easily scheduled by going to the Data Transformation

Services group | Local Packages, then right-click on the package to schedule and

select Schedule. There is also version control built into saving a package here.

As each package is saved here, it is automatically version controlled. You will

want to occasionally audit this though and purge the old versions, as it will

indefinitely keep all the past versions and eventually filling your msdb

database.

Deleting Old Versions

As I mentioned earlier, if you're an active DTS developer, you can easily

fill up your msdb database as you save large packages. To prevent this from

happening, you will have to occasionally purge old versions of your package

manually. To purge the old versions, right-click on a package and select

Versions. Then select the older version you'd like to purge and click Delete.

Notice in this screen here you can also click Edit to roll-back to an earlier

version.

Meta Data Repository

Meta Data Repository allows you to self-document a package. It will scan your

package and document key items, like connections and tasks. Although I see the

purpose of the Meta Data Repository, I'm against saving any DTS packages in it.

This is mostly because I've been burned by it in the past. Burned once, shame on

SQL Server, burned twice, shame on me. The reason for my dislike of this method

is because saving your packages here will slow them down immensely as well as

increase the risk of corruption. Yes, I said corruption of the package. I've

seen too many packages that are saved here suddenly become inaccessible. You

also cannot secure these packages with User and Owner passwords.

COM-Structured File

Saving a package as a COM-Structured file is the quickest way to save an load

a package. It is also the most portable because you can save it on a disk and

take it easily to another computer. To load a package that has been saved as a

file (.DTS extension), you will need to open Enterprise Manager then right-click

on Data Transformation Services and select Open Package.

One of the reasons I save my packages in this format versus the others is

because I can version control the package with a stronger method like Source

Safe. Even though DTS will self-version control itself, it does a poor job at

best of doing so. In this release of SQL Server, it doesn't come close to Source

Safe.

Deleting Old Versions

When you save a package as a file, it becomes more difficult to delete old

versions of the package because there is no GUI method to purge the old

versions. Your DTS files can grow to be quite large (most of mine are over 1.5

megs). This can become a problem if you have a package that is a megabyte and

each time you save it, it will grow by another megabyte.  Since I save my

packages quite often out of paranoia, mine grow to be 100 MB in one day before I

purge the old versions. This makes your packages much less portable and they

will load slower.

The workaround is to open the file in DTS Designer and then, while the

package is open, delete the file. Then in DTS Designer, select Package | Save As

and give the package its original name. Keep in mind that this workaround will

purge all old versions of the package.

VB File

The final place to save your packages as is a VB file. Saving your packages

here will save them as a .BAS file that can be viewed in Notepad (or any text

viewer) or Visual Basic. This is a great way to learn the object model for DTS

and provides you an easy way to create a Visual Basic component that can be

pasted into your Visual Basic program. The only problem with this is that as

soon as you save a package in this format, you cannot re-open the .BAS file in

DTS Designer. This makes it very difficult to edit the package after it is

saved. With that said, if you're going to save a package here, always keep a

backup in another of the 4 formats so you can modify the packages easily in the

GUI DTS Designer.

Optimizing Package Loads

Loading a package in Designer is notoriously slow. This is because DTS

Designer must go out and scan the registry looking for the OLE DB providers,

just in case any have been added. Beginning in SQL Server 2000, you have the

option to load into cache the providers. This speeds up the DTS client a great

deal. To do this, right click on Data Transformation Services in Enterprise

Manager and check Turn on Cache as shown below.  Keep in mind that each

time that you add a provider, you will want to ensure that you click Refresh

Cache on the same screen.

Location Pros and Cons