DTS versioning

I'm having problems to keep proper versioning control in place between the development and production environments. I'm running SQL2000 and we have hundreds of packages that runs daily. Some on shedules and some not.

Every time a package is saved, it creates a version in SQL Server. After development I want to be able to use something like "Visual Source Safe" and book the final version in that was moved into production. Something with a version number etc.

This is especially a problem if I want to roll back to a prior version of a package. I do not know which one of the 1000 versions to choose from that were created while developing the package.

Another problem is that I do not know if someone is working on a package if I want to work on it.

I can not run a search on all the packages to get a list of which tables/fields are used where to determine the impact of a program/database/design change that needs to be implemented.

Any suggestions would be helpfull.

You could use DTS file packages, and keep then under source control.

I would have a defined migration process, such that when promoting to an environment, that release or build is packaged into a unit that can be added to source control. In practice, take file copies of the packages and add them to VSS. Don't expect a nice integrated approach to source control and project code, it won't happen. You need to have the discipline to manage this manually but it can still be effective.

Treat the server versions as your local rollback, when developing in between saves, do not use it as a source control, since you have no tracking or tagging. Each time you deploy, deploy from the packaged release, not the dev server. They just do not work for development control, so think of them as a local factor, or just ignore them. Each developer should build packages on his local workstation anyway I think, so it should not be a conflict issue, as they will have checked out the latest release file to start work anyway

You may find my DTSBackup 2000 tool useful for grabbing files, (http://www.sqldts.com/242.aspx)