DTS: How to connect to hundreds of DBs dynamically

I need to release a few triggers and procedures to hundreds of databases
with the same structures.
I try the Dynamic Property in DTS, create an "Execution Task", add a
connection property with a source type as "Query", and the query is like
"select the database name from master..sysdatabases where name not in
('master','model','temp')".
Unfortunately, the package runs only on the first DB in the query.
How can I make it run on each of all databases?
Thank you.Create a connection to the server,
create an "Execute SQL Task" with your databases query in it -
in the "Output parameters" tab of the sql task use a rowset output
then loop through the values in an ActiveX script to perform your jobs.
http://www.sqldts.com/default.aspx?298
"Jluo" <jluost1@.yahoo.com> wrote in message
news:4224B876.5000304@.yahoo.com...
> I need to release a few triggers and procedures to hundreds of databases
> with the same structures.
> I try the Dynamic Property in DTS, create an "Execution Task", add a
> connection property with a source type as "Query", and the query is like
> "select the database name from master..sysdatabases where name not in
> ('master','model','temp')".
> Unfortunately, the package runs only on the first DB in the query.
> How can I make it run on each of all databases?
> Thank you.
>

DTS w/dynamic source & query

I'm looking for a way to transform the contents of n source tables
into a single destination table. This by itself is no problem.

However, the name of the source tables change, so I'll need to base
the transform task on a global variable that I can update via external
code. Not sure how to do that. I'm ok with executing the package 10
times if there's 10 source tables.

The last unknown piece is modifying the query used for the transform.
There are 10 columns in the source table, but there are 12 columns in
the destination table. I must provide the 2 missing columns. They will
simply contain a year and month, ie. 05 2003.

I'm taking a bunch of source tables (for a given month and year) and
rolling them together into one destination table, and carrying over
the month and year. I assume the month and year would also be globals.
But I'm not sure how to incorporate them into the transform task since
it wants strict SQL syntax.

Any help is appreciated, thanks in advance!"znelson" <znelson@.hpis.net> wrote in message
news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> I'm looking for a way to transform the contents of n source tables
> into a single destination table. This by itself is no problem.
> However, the name of the source tables change, so I'll need to base
> the transform task on a global variable that I can update via external
> code. Not sure how to do that. I'm ok with executing the package 10
> times if there's 10 source tables.
> The last unknown piece is modifying the query used for the transform.
> There are 10 columns in the source table, but there are 12 columns in
> the destination table. I must provide the 2 missing columns. They will
> simply contain a year and month, ie. 05 2003.
> I'm taking a bunch of source tables (for a given month and year) and
> rolling them together into one destination table, and carrying over
> the month and year. I assume the month and year would also be globals.
> But I'm not sure how to incorporate them into the transform task since
> it wants strict SQL syntax.
> Any help is appreciated, thanks in advance!

One way to pass in a global variable at runtime is as a parameter to
dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
you, then parameterize it using your preferred language:

http://www.sqldts.com/default.aspx?301

This is useful when the number of parameters is fairly limited. An
alternative if you have many global variables is to put the values in a
database table or .INI file, and use a Dynamic Properties task to retrieve
them and assign them to the variables. This allows for more complex logic,
conditional values based on server name, AD site name etc. Finally, you can
assign global variable values directly in an ActiveX task.

As for how to pass in the month/year to an INSERT, these pages may be
useful:

http://www.sqldts.com/?234
http://www.sqldts.com/?205

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<409a6999$1_2@.news.bluewin.ch>...
> "znelson" <znelson@.hpis.net> wrote in message
> news:f5fff72a.0405060627.796ea9cb@.posting.google.c om...
> > I'm looking for a way to transform the contents of n source tables
> > into a single destination table. This by itself is no problem.
> > However, the name of the source tables change, so I'll need to base
> > the transform task on a global variable that I can update via external
> > code. Not sure how to do that. I'm ok with executing the package 10
> > times if there's 10 source tables.
> > The last unknown piece is modifying the query used for the transform.
> > There are 10 columns in the source table, but there are 12 columns in
> > the destination table. I must provide the 2 missing columns. They will
> > simply contain a year and month, ie. 05 2003.
> > I'm taking a bunch of source tables (for a given month and year) and
> > rolling them together into one destination table, and carrying over
> > the month and year. I assume the month and year would also be globals.
> > But I'm not sure how to incorporate them into the transform task since
> > it wants strict SQL syntax.
> > Any help is appreciated, thanks in advance!
> One way to pass in a global variable at runtime is as a parameter to
> dtsrun.exe - you can use dtsrunui.exe to build a sample command line for
> you, then parameterize it using your preferred language:
> http://www.sqldts.com/default.aspx?301
> This is useful when the number of parameters is fairly limited. An
> alternative if you have many global variables is to put the values in a
> database table or .INI file, and use a Dynamic Properties task to retrieve
> them and assign them to the variables. This allows for more complex logic,
> conditional values based on server name, AD site name etc. Finally, you can
> assign global variable values directly in an ActiveX task.
> As for how to pass in the month/year to an INSERT, these pages may be
> useful:
> http://www.sqldts.com/?234
> http://www.sqldts.com/?205
> Simon

Simon,

Thanks a lot, very helpful!