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
