I am embarking on a project where it may require 10-15 packages to
load and scrub a data set. As we add more platform sources that we
load from, a new set of packages will be required, so the total number
of packages will tend to grow arithmetically. We anticipate the need
to be constantly evolving, and improving a large portion of the
packages. If this were being done in compiled languages, I know I
could count on something like Visual Source Safe to help me manage
revision history, which could be very valuable in tracking down
problems. Is there any such similar technology or methodologies
available for systems developed in DTS?

TIA - Best regards, Lee Gillie, Spokane WA

In article <#l5Q7$cvDHA.1680 (AT) TK2MSFTNGP12 (DOT) phx.gbl>, Lee Gillie
<ANTISPAMIFICATION_lee (AT) odp (DOT) com> writes
If using "Local Server" then each "Save" produces a new version.
Packages are stored in sysdtspackages, one row per version. In EM you
can right click to view and load a specific version. This may be
complicated when using things like the Exec Pkg task as you will have to
specify the version, otherwise you get the default. Obviously since this
is just a SQL table you could overlay your own processes via TSQL to
ensure the correct version is available in sysdtspackages.

A better option may be to save all packages as files, and use VSS as
normal.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote in
message news:vgwfhCVgPP1$Ew67 (AT) sqldts (DOT) com...
Ok, I am thinking I could create a tool that would move packages from
developer's desktops to the production server, and at the same time
save the package as BAS, and return this to VSS. This would allow use
of the "source code differences" tool in VSS to see an approximation
of what is different between two versions of a package, when analyzing
problems. VSS could also be the arbiter of who has a package checked
out for edting.

Is my desire for this unique?

On Mon, 8 Dec 2003 13:12:24 -0800, "Lee Gillie" wrote:

G'day Lee,

As Darren says, multiple versions are saved in SQL Server (version is
incremented each time you enter the Designer and save; multiple saves
from a Designer session do not result in multiple versions). Also,
saving to an existing Structured Storage File (SSF) will put a new
version into that file (one SSF can contain multiple versions of a
package).

However, this doesn't work in well with software deployment, because you
can't just extract a complete set of objects from your version control
system and deploy them - you need to grab from the SQL Server also.

What I do is Save As - Structured Storage File (SSF), and Save As -
Visual Basic (VB). This gives me two files that I can save in my SCM /
version control system. I want both, because the SSF is the easy one to
use for deployment, and the VB one allows me to do simple version
comparisons so that I can see what has changed.

Note that as previously stated, saving to an existing SSF will store a
new version of your package alongside the existing version(s) in there.
This will, of course, lead to a large bloated SSF file over time, which
you don't need if you are storing individual versions in your version
control system. What I do is when I Save As - SSF, I navigate to my
working folder, select the existing file, and delete it from within the
Save As dialog, then save.

Another solution is to build the packages in code. I do this in one
situation, mostly because it allows me to build the packages
conditionally (i.e. different steps and transforms depending on what is
required at the time), but this also nicely integrates in with the
software version control - because it is part of the software source
code. This might also be an option for you if you have slightly
different deployment requirements: rather than create and manage
multiple packages, it might be easier to conditionally build one package
in code. For an example of building packages in code, just Save As - VB
and read.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello

If you SAVE AS VB, how would you then upload the VB code as a package that
can be edited via SQLEM ?
"Ross McKay" <rosko (AT) zeta (DOT) NOT.THIS.BIT.org.au> wrote

In article <ejQ#qonvDHA.2352 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Jeff Block
<jblock (AT) REMOVETHECAPSANDINVALIDfirstlook (DOT) biz.invalid> writes
You have to compile the VB code and execute it to rebuild the package.
As part of the standard VB generated code there are two final options,
either Execute the package or SaveToSQLServer, I forget which is the
default and the other is commented out. The latter would be used to
re-create the package and save it back to SQL Server for EM access. You
could of course use a SaveToStorageFile instead though.

Ross's method involved performing two Save As's, one for SSF and one for
VB so he had both, so in theory he has both code and SSF copies of each
version.

Personally I'm not sold on the idea of two manual saves that must be in
sync, but it obviously can work. One thing I would consider if using
this approach would be to automate the Save As VB, though the old
ScriptPkg tool. There is no SaveAsVB object model method. ScriptPkg is
SQL 7.0 code but it can be extended for SQL 2000.

Another comparison option is the DTS Compare tool from Red-Gate -
http://www.red-gate.com/sql/DTS_Compare.htm

There is no right answer here, just a case of what works best for each
organisation and their change control processes.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

On Tue, 9 Dec 2003 18:00:50 +0000, Darren Green wrote:

For me at least, the VB code is a convenience - it helps me to identify
*exactly* what has changed between versions of a package. I mainly use
it as a form of code review if you like, before I commit a changed
package to the version control system.

If there is any doubt about the two formats being in sync, it is trivial
to load the SSF into Enterprise Manager and Save As VB. For me, the
"source of truth" is the SSF.

Thanks, will check that out.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander