SSRS - getting the ACTUAL connection strings for ALL your datasources in SSRS

File under "and this is why I'm not an MVP"...

Yeah, it's been a few months. Azure, Powershell, Python, Parquet, and the entire team turning over. 

However, now I get to deal with... SSRS?  Really?  Yeah, until we figure out how to convert them to PowerBI or something Azure-centric that requires no effort.  Seriously, I can't believe Microsoft doesn't have this.  It's DUM. D-U-M-B, dum.

But I digress.

Today: comparing your old SSRS Datasource connection strings to the new server, since they don't match and you don't think you can just backup/restore over ReportServer.

<script src="https://gist.github.com/mbourgon/b0fb93736354040ce7242673f8e90107.js"></script>

and , in case THAT doesn't work.

----------------------------------------------------------

#to install the MS module you need:

#Invoke-Expression (Invoke-WebRequest https://raw.githubusercontent.com/Microsoft/ReportingServicesTools/master/Install.ps1)

$SSRS_Servername = "yourservernamehere"

#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.

Requieres 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;

"@

. C:\Powershell_Scripts\invoke-sqlcmd2a.ps1

$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