Hello everyone and Happy New Year!

I have a need to query application databases across multiple servers for row
counts, object counts, etc. In the past I have used Query Manager to do
this server by server.

I would like to write a DTS package on a central server that uses a source
file as input into the DTS Source (what server to connect to), and input
into the query (what database to query). For example, the source file could
look like 'server\instance name, database'. The DTS Source would pick up
the server name and connect with Windows Authentication. The actual DTS
query would pick up the database name and run the query against that
database. (Output for the query would be stored in a table on the central
server).

The query is written, works fine..but I seem to be struggling with how to
use that input source file to populate the DTS Source and the DTS Query.
I'm talking about hundreds of server\database combinations that I need to
query each month. If I could figure this out, this would be slick.

I thank anyone in advance for advice.

Steve

Hi,

For Populating Source information at runtime, you can use INI File to put all your source information.\

INI file can be accessible via global variables.

Regards
Bharat Jariwala

************************************************** ********************
Sent via Fuzzy Software @ http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Hello Steve,

One way of doing this would be to store your configuration information in
a database table. You can then loop over this database table and on each
iteration of the loop you can set the properties of an ExecuteSQL task.
This task issues the relevant statement to get the info you want and does
with it as you see fit.

Here is an example.

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)


Allan