Skip to content

Instantly share code, notes, and snippets.

@marcosfreccia
Last active April 18, 2024 16:48
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save marcosfreccia/a01d5e02c76ed2f325b08ebef35407e2 to your computer and use it in GitHub Desktop.
Save marcosfreccia/a01d5e02c76ed2f325b08ebef35407e2 to your computer and use it in GitHub Desktop.
This script helps you to link Data Sources to SSRS after a migration to a different report Report Server.
$TargetReportServerURL = "http://MySSRSServer/ReportServer"
$ReportServer = "MySqlServerInstance"
$ReportServerDB = "ReportServer"
$RootFolder = "/MyReports"
$DataSourceUrlPath = "/MyReports/Data Sources/SalesDataSource"
<#
This query assumes that in the $RootFolder you have reports that links to only a single Data Source.
If you have in the same $RootFolder reports that have different data sources, you would a new parameter
and also change your WHERE clause to add this possibility.
#>
$Reports = Invoke-sqlcmd2 -ServerInstance $ReportServer -Database $ReportServerDB -Query "SELECT C.Name AS report_name,
DS.Name AS data_source_name,
PARENT.Path + '/' + C.Name AS full_report_url
FROM dbo.Catalog C
INNER JOIN dbo.Catalog PARENT
ON PARENT.ItemID = C.ParentID
INNER JOIN dbo.DataSource DS
ON C.ItemID = DS.ItemID
LEFT OUTER JOIN dbo.DataSets DSET
ON DSET.LinkID = C.ItemID
LEFT OUTER JOIN dbo.DataSource DSET_SRC
ON DSET_SRC.ItemID = DSET.ItemID
WHERE C.Type = 2
AND PARENT.Path LIKE '$RootFolder%';"
foreach ($report in $Reports) {
try {
$FormatedReport = $report.full_report_url
$data_source = $report.data_source_name
Set-RsDataSourceReference -ReportServerUri $TargetReportServerURL -Path $FormatedReport -DataSourceName $data_source -DataSourcePath $DataSourceUrlPath -Verbose
}
catch {
return $FormatedReport
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment