Hi,

We have several large and complex DTS packages for converting from OLTP to
Star schema, and maintaining them on SQL server is becoming quite
cumbersome. Additionaly, we have to change the server/db names and other
parameters for different client installations and we're currently doing this
using INI files. We have multiple developers working on the same set of pkgs
and, needless to say, version control is a nightmare.

I'm now thinking about migrating the whole thing to VB6 projects and
maitaining the packages entirely in code. Does anyone know of any caveats in
going this route? What is the general consensus among the DTS gurus on
maintenance of large/complex DTS pkgs, with performance being an important
factor?

Here's a summary of issues I can think of:

Advantages of maintaining in SQL Server:
.. Visual interface
.. Saving of layout
.. Ease of use
.. Easy to deploy


Disadvantages of SQL Server/ Advantages of maintaining as VB6 code:
.. Source code comparison across versions
.. Flexibility (SQL and other common parameters can be changed easily and at
runtime)
.. More powerful(??)
.. Easier Debugging
.. Easier maintenance - By this I mean that we can see all SQL queries, table
names etc in a single file, without having to open and close all those
individual dialog boxes.

Are there any other considerations I need to think about?

Thanks for your input,
Chumma Dede

Chumma,

I think going to VB is an indicator of panic. For now DTS is saved in VB6.
In a year MS is going to drop VB6 support. Converting to .NET may cost
you... Besides, VB6 does not support multithreading unless you go to
ActiveX.EXE. With regards to server name change you can establish server
alias and maintain server name stability that way. Client Network Utility
will help you there. As to multiple developers working on the same set of
pkgs it is a matter of discipline. Use SourceSafe/simple spreadsheet to mark
an item as being worked on. Same SourceSafe allows to compare differences
between versions of DTS saved as VB files.

Ilya

"Chumma Dede" <deja.removethis (AT) chothu (DOT) mailshell.com> wrote

Multiple developers - hold packages in sourcesafe.
Also load from files - you will find it much easier.

Different databases/servers.
Look at the dynamic properties task. You can use this to set the server/database at run time.
In an environment with a lot of dts packages I usually create a loader which gets that info from a table and sets it in the package before running - and does some other stuff like setting general properties and logging.

"Chumma Dede" wrote:

Chumma
We use udl files to store the db connection information and have
come up with a standard file location that are packages point to. We
store dynamic information in metadata table(s) and then use dynamic
properties task or a SQL query task to populate the properties.

As far as multiple developers we use sourcesafe and the DTSBackup
utility to load and unload the DTS packages between SQL and VSS as we
have over 180 packages.

-- Norman

Chumma Dede wrote: