There is a requirement to have a high level overview of all project sites within our Sharepoint / Office 365 environment.
Currently projects exist across 5 site collections, with each site collection having multiple project sub-sites. As far as I am aware, all the project sub sites are created from the default template and have not been modified so all should have an identical structure.
What I would like to be able to do is extract the data from the "Project Plan" lists in each of these sub sites which could be imported into our SQL Server environment to be manipulated and produce a dashboard. The export/import process would ideally be handled by SQL Server Integration Services (SSIS).
What options do I have available to be able to do something like this and is it possible to have a solution that would be dynamic and add in new project sites as they are created? I am not skilled enough in SharePoint development to use XLST, SharePoint Designer to come up with a web-part that may be able to do this.