Saving DTS Packages Outside of SQL Server
Introduction
One of the things that I rarely see people do is save a copy of their DTS packages to the filesystem,
as opposed to saving them inside SQL Server. I am guilty of this myself, after all, the default is to
save the package to SQL Server and it is easy to do.
However, if you have problems, or get a corrupt package, which is something that happens to me occassionally, what
can you do? In the past I have usually had to rebuild the package by hand. This is fine for simple packages,
but is time consuming for larger ones and a bad idea.
What about version control?
SQL Server keeps previous versions of saved packages in msdb (did you know this?). But this doesn't help
when we are deploying packages across servers. I usually take my packages through a development and test cycle
before moving them to the live servers, so I sometimes lose some of the version control by depending on SQL Server.
Since I am somewhat paranoid, I like to have all my stuff under version control in a separate place from
SQL Server, just in case.
Save Locally
Fortunately, SQL Server allows you to save packages to the local file system. This is easy enough to
accomplish. The standard "save" dialog looks like this:
However, by changing the drop down box from "SQL Server" to "Structured Storage File", you get the
following dialog:
By entering the path to a file, you can save the package in a format that can be placed in a version control
system. This format can be executed using the command line DTSRUN.EXE utility or opened and saved on another
SQL Server.
Conclusions
Nothing earth shattering in this article, but if have never used the save locally feature, it is a nice
option to have and one that you should take advantage of. As always, please rate this article and submit any feedback
using the tab below.
Steve Jones
©dkRanch.net July 2001