mbourgon · January 21, 2022 21:35
diff --git a/ssrs_actual_datasource_info.ps1 b/ssrs_actual_datasource_info.ps1
 #What is this? Uses the microsoft tool & API & SQL Server to figure out ALL your actual datasources.
 #As per the stackoverflow below, you CANNOT just use the RDLs - they don't get updated if you do it through the GUI.
 #The Microsoft tool works great, but does them one at a time. So by combining the two, hopefully you get the best of both worlds.

 #Note - there are two prerequisites.
 #1 - invoke-sqlcmd2. Invoke/Import yourself; not doing it in here. Find on github. You might be able to use invoke-sqlcmd, but I never use it.
 #2 - Microsoft's ReportingServicesTools, available on Github
 #to install #2 (as of 2022): Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

 $SSRS_Servername = "yourservernamehere" #assumes ReportServer for name.  Aka when you go to the website, you should be able to http://yourservernamehere/ReportServer

 #query cribbed from https://dba.stackexchange.com/questions/138236/get-ssrs-datasources-from-reportserver
 $SSRS_list_datasources = @"
 /*
 Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
 With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,
 to document the usage or to search for a specific server/database.

 Please remark: Querying the ReportServer database directly is not a supported way.
 Works with SSRS 2005 and higher version ReportServer databases.
 Requires select rights on the "Catalog" table in ReportServer database.
 */

 -- Connection strings of all SSRS Shared Datasources
 ;WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
            ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
     AS rd)
 ,SDS AS
    (SELECT SDS.name AS SharedDsName
           ,SDS.[Path]
           ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
     FROM dbo.[Catalog] AS SDS
     WHERE SDS.Type = 5)     -- 5 = Shared Datasource

 SELECT CON.[Path]
      ,CON.SharedDsName
      ,CON.ConnString
 FROM
    (SELECT SDS.[Path]
           ,SDS.SharedDsName
           ,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
     FROM SDS
          CROSS APPLY 
          SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
     ) AS CON
 -- Optional filter:
 -- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
 ORDER BY CON.[Path]
        ,CON.SharedDsName;
 "@

 $datasource_list = invoke-sqlcmd2 -query $SSRS_list_datasources -ServerInstance $SSRS_Servername -Database ReportServer

 $DataSource_Details = @()
 $DataSource_Details += foreach ($datasource in $datasource_list) {
 $ConnectString = Get-RsDataSource -ReportServerUri "http://$SSRS_Servername/ReportServer" -Path $datasource.Path
 $connectstring|select @{Label='SharedDsName';Expression={$datasource.SharedDsName}} ,@{Label='Path';Expression={$datasource.path}}, @{Label='ConnectionString';Expression={$_.ConnectString}} , @{Label='Username';Expression={$_.UserName}} 
 }

 $DataSource_Details|format-table
	#What is this? Uses the microsoft tool & API & SQL Server to figure out ALL your actual datasources.
	#As per the stackoverflow below, you CANNOT just use the RDLs - they don't get updated if you do it through the GUI.
	#The Microsoft tool works great, but does them one at a time. So by combining the two, hopefully you get the best of both worlds.

	#Note - there are two prerequisites.
	#1 - invoke-sqlcmd2. Invoke/Import yourself; not doing it in here. Find on github. You might be able to use invoke-sqlcmd, but I never use it.
	#2 - Microsoft's ReportingServicesTools, available on Github
	#to install #2 (as of 2022): Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

	$SSRS_Servername = "yourservernamehere" #assumes ReportServer for name. Aka when you go to the website, you should be able to http://yourservernamehere/ReportServer

	#query cribbed from https://dba.stackexchange.com/questions/138236/get-ssrs-datasources-from-reportserver
	$SSRS_list_datasources = @"
	/*
	Let's say you want to move a database to an other SQL Server, but which of the SSRS Shared Datasources uses this database and must be changed afterwards?
	With this Transact-SQL query for ReportServer database you get the connection string of all Shared Datasources,
	to document the usage or to search for a specific server/database.

	Please remark: Querying the ReportServer database directly is not a supported way.
	Works with SSRS 2005 and higher version ReportServer databases.
	Requires select rights on the "Catalog" table in ReportServer database.
	*/

	-- Connection strings of all SSRS Shared Datasources
	;WITH XMLNAMESPACES -- XML namespace def must be the first in with clause.
	(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
	,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
	AS rd)
	,SDS AS
	(SELECT SDS.name AS SharedDsName
	,SDS.[Path]
	,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
	FROM dbo.[Catalog] AS SDS
	WHERE SDS.Type = 5) -- 5 = Shared Datasource

	SELECT CON.[Path]
	,CON.SharedDsName
	,CON.ConnString
	FROM
	(SELECT SDS.[Path]
	,SDS.SharedDsName
	,DSN.value('ConnectString[1]', 'varchar(150)') AS ConnString
	FROM SDS
	CROSS APPLY
	SDS.DEF.nodes('/DataSourceDefinition') AS R(DSN)
	) AS CON
	-- Optional filter:
	-- WHERE CON.ConnString LIKE '%Initial Catalog%=%TFS%'
	ORDER BY CON.[Path]
	,CON.SharedDsName;
	"@

	$datasource_list = invoke-sqlcmd2 -query $SSRS_list_datasources -ServerInstance $SSRS_Servername -Database ReportServer

	$DataSource_Details = @()
	$DataSource_Details += foreach ($datasource in $datasource_list) {
	$ConnectString = Get-RsDataSource -ReportServerUri "http://$SSRS_Servername/ReportServer" -Path $datasource.Path
	$connectstring\|select @{Label='SharedDsName';Expression={$datasource.SharedDsName}} ,@{Label='Path';Expression={$datasource.path}}, @{Label='ConnectionString';Expression={$_.ConnectString}} , @{Label='Username';Expression={$_.UserName}}
	}

	$DataSource_Details\|format-table