execute rss script on several servers using SSIS, storing results in a table
I found a wonderful script that collects all the (shared) datasources used on a reportserver:
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:
- Loop through table with servers
- execute script (see link)
- insert resulting csv into central table (preferably skip this step, have script insert data in table directly)
- 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,
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:
- Create a foreach loop which iterates over a list or ado.net recordset of your servers
- Put the server name in a variable
- Create a variable for the arguments for the process task, referencing the server variable from step 2
- Add a process task which uses the above argument and calls rs.exe