Does anyone use SSIS for database schema maintenance?

We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.

We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.

Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.

ANY information at all would be extremely appreciated!On Feb 28, 2:54 pm, "Ben" <vanev...@.gmail.comwrote:

Quote:

Originally Posted by

We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.
>
We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.
>
Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.
>
ANY information at all would be extremely appreciated!

Hi Ben,

There is a rock solid change management process for SQL Server
2000/2005 and it is provided by the DB Ghost toolset from
Innovartis.

The essence of the process is that you script out all the database
objects and lookup (static) data into individual CREATE / INSERT
scripts and put them under source control. The whole dev team then
checks these files out, makes the required changes to the CREATE
statements and checks them back in again (this can scale to thousand
of developers). Once you're ready to release the schema to the test
environment you use the DB Ghost Change Manager tool to make the
target database match the set of source scripts. If, for example, a
developer added a column to a table CREATE script then the Change
Manager would detect this and add the column to the target database
seamlessly.

Basically, DB Ghost enables you to develop in the same way as you do
for a greenfield (release 1) database for every subsequent release of
your schema without losing any data in the target database.

Our customers rave about DB Ghost and can't believe the cost savings
it brings - have a look for yourself :)

www.dbghost.com
Kind regards,

Malcolm