Can I 'script' a DTS package?

[billyackerman]

Any advice appreciated...

I am trying to compile a list of users (among other things) that have access to SQL Server. We have multiple Servers (20+). I would like to compile the list into one database on one Server. I have written a small script that will 'select' the information to insert from the correct table. So far so good.

Here is my problem. I can run the script through a DTS package and get the information from the other Server and insert the information into 'my' Server. However, I do not know how to run ONE script to get all of the information from ALL Servers. Does that make sense? I think that I could 'link' Servers and do the same thing, but as I said in an earlier cry for help, my supervisor is not big on linking tables.

I would appreciate any advice or guidance. Thanks

[Phil Mc]

How about using VB Script from within the DTS Package. Make a list of servers in "MyServer". Connect to "MyServer", retrieve the servername, then loop through them, making connections, running the select against each server and inserting into "MyServer". Use an ADO connection to MyServer and create a new one onto each Server from your ado recordset.

Hope this is a help.


------------
billyackerman at 6/13/01 9:34:05 AM


Any advice appreciated...

I am trying to compile a list of users (among other things) that have access to SQL Server. We have multiple Servers (20+). I would like to compile the list into one database on one Server. I have written a small script that will 'select' the information to insert from the correct table. So far so good.

Here is my problem. I can run the script through a DTS package and get the information from the other Server and insert the information into 'my' Server. However, I do not know how to run ONE script to get all of the information from ALL Servers. Does that make sense? I think that I could 'link' Servers and do the same thing, but as I said in an earlier cry for help, my supervisor is not big on linking tables.

I would appreciate any advice or guidance. Thanks