execute rss script on several servers using SSIS, storing results in a table

Question

I found a wonderful script that collects all the (shared) datasources used on a reportserver:

LINK

I simply love this script.

However, I am looking for a way to execute this script on several reportservers and add the results to a centralised table. That way my colleagues and me would be able to see pretty quickly what datasources are used.

I could place this script on each reportserver, collect the csv's on a central server and then use SSIS to insert them into a MSSQL table. That way I would have a nice central overview of all the used datasources.

However, I would prefer to have the script in one location and then execute that script on a list of servers. Something like:

1. Loop through table with servers
2. execute script (see link)
3. insert resulting csv into central table (preferably skip this step, have script insert data in table directly)
4. next server

Any suggestions as to what the best approach would be? Should it be a webservicetask? Scripttask? Something else completeley?

The level of scripting in the mentioned script is right at the edge of what I understand, so if someone would know how to adapt the script in such a way that I could use it as input in a dataflow in SSIS I would be very happy.

Thanks for thinking with me,

Henro

Answer 1

This script is called using a utility called rs.exe so you would use an execute process task to call it. To avoid writing to a file, you could modify the script and have it insert the results into a table. The package could be set up as follows:

1. Create a foreach loop which iterates over a list or ado.net recordset of your servers
2. Put the server name in a variable
3. Create a variable for the arguments for the process task, referencing the server variable from step 2
4. Add a process task which uses the above argument and calls rs.exe