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

Does a Job execute MUCH faster??

Hi,

Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.

However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.

Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.

I'll appreciate comments. ThanksIt's actually a little simpler than that. The same exact set of processes are involved in running your package(-s) interactively or as a scheduled job. The difference lies in the fact that, while being scheduled, the package runs on the server, and any and all output streams are within the "server reach" (in other words nothing leaves the box), running the same package interactively causes every output flag (such as the number of rows processed so far) to be sent to the client.|||Thanks for the reply :)

Documenting SQL 2005 packages

Hi all

I would like to select transforms in a package and paste them as jpeg or similar into word or powerpoint in order to allow me to create a separate set of documentation.

However, whenever I copy some transformations and jump to word, the copy buffer is empty.

Any suggestions?

Thanks

I am not sure you can do what you are after, but I am intrigued. What do you mean by copy a transform? What do you expect to see?

|||

I would like to be able to paste the 'graphical' representation of a set of SSIS objects into a word or ppt document as a jpeg or similar.

|||

Does print screen not work for you?

-Jamie

|||

Hi Jamie

The problem with print screen are:

* Firstly, the quality of the image is quite poor by the time it is dumped into word,

* But, more importantly, if all the objects don't fit on the screen at one time, then print screen wont capture them.

I could easily solve the 2nd problem by zooming out, but then I lose all detail and defeat the purpose.

A number of my data flows have 20+ objects in them and won't fit on a single A4 page.

I would like to paste an image of them onto an A3 page in word or ppt so that I can include them in the warehouse documentation.

|||

I had thought of grabbing the graphics and making them into some Visio Shapes, that would allow graphical documentation of packages, but they are not my images to do with as I wish! MS?

|||

I know it seems a little bit of a strange thing to want to do, but I am only building the warehouse and will (hopefully) take no part in the adminstration.

So, in order to provide our DBA with a fighting chance, I want to do my utmost to give as much help as possible as to the inner workings of the packages.