我想在一个服务器上获取所有.rdl文件的副本。
我可以手动逐个下载报告,但这很耗时,特别是这个服务器有约1500个报告。
是否有任何方法或工具可以下载所有.rdl文件并复制它们?
使用PowerShell有一种完整且更简单的方法来完成此操作。
此代码将导出所有报告内容,其结构与报告服务器完全相同。请查看Github wiki获取其他选项和命令。
#------------------------------------------------------
#Prerequisites
#Install-Module -Name ReportingServicesTools
#------------------------------------------------------
#Lets get security on all folders in a single instance
#------------------------------------------------------
#Declare SSRS URI
$sourceRsUri = 'http://ReportServerURL/ReportServer/ReportService2010.asmx?wsdl'
#Declare Proxy so we dont need to connect with every command
$proxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri
#Output ALL Catalog items to file system
Out-RsFolderContent -Proxy $proxy -RsFolder / -Destination 'C:\SSRS_Out' -Recurse
Add-Type -AssemblyName "System.IO.Compression.Filesystem"
$dataSource = "SQLSERVER"
$user = "sa"
$pass = "sqlpassword"
$database = "ReportServer"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pass;Database=$database;Integrated Security=False;"
$tempfolder = "$env:TEMP\Reports"
$zipfile = $PSScriptRoot + '\reports.zip'
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$allreports = $connection.CreateCommand()
$allreports.CommandText = "SELECT ItemID, Path, CASE WHEN Type = 2 THEN '.rdl' ELSE '.rds' END AS Ext FROM Catalog WHERE Type IN(2,5)"
$result = $allreports.ExecuteReader()
$reportable = new-object "System.Data.DataTable"
$reportable.Load($result)
[int]$objects = $reportable.Rows.Count
foreach ($report in $reportable) {
$cmd = $connection.CreateCommand()
$cmd.CommandText = "SELECT CAST(CAST(Content AS VARBINARY(MAX)) AS XML) FROM Catalog WHERE ItemID = '" + $report[0] + "'"
$xmldata = [string]$cmd.ExecuteScalar()
$filename = $tempfolder + $report["Path"].Replace('/', '\') + $report["Ext"]
New-Item $filename -Force | Out-Null
Set-Content -Path ($filename) -Value $xmldata -Force
Write-Host "$($objects.ToString()).$($report["Path"])"
$objects -= 1
}
Write-Host "Compressing to zip file..."
if (Test-Path $zipfile) {
Remove-Item $zipfile
}
[IO.Compression.Zipfile]::CreateFromDirectory($tempfolder, $zipfile)
Write-Host "Removing temporarly data"
Remove-Item -LiteralPath $tempfolder -Force -Recurse
Invoke-Item $zipfile
https://microsoft-bitools.blogspot.com/2018/09/ssrs-snack-download-all-ssrs-reports.html
如果链接失效,这里是链接中的代码:###################################################################################
# Download Reports and DataSources from a SSRS server and create the same folder
# structure in the local download folder.
###################################################################################
# Parameters
###################################################################################
$downloadFolder = "c:\temp\ssrs\"
$ssrsServer = "http://myssrs.westeurope.cloudapp.azure.com"
###################################################################################
# If you can't use integrated security
#$secpasswd = ConvertTo-SecureString "MyPassword!" -AsPlainText -Force
#$mycreds = New-Object System.Management.Automation.PSCredential ("MyUser", $secpasswd)
#$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -Credential $mycreds
# SSRS Webserver call
$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -UseDefaultCredential
# List everything on the Report Server, recursively, but filter to keep Reports and DataSources
$ssrsItems = $ssrsProxy.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "DataSource" -or $_.TypeName -eq "Report"}
# Loop through reports and data sources
Foreach($ssrsItem in $ssrsItems)
{
# Determine extension for Reports and DataSources
if ($ssrsItem.TypeName -eq "Report")
{
$extension = ".rdl"
}
else
{
$extension = ".rds"
}
# Write path to screen for debug purposes
Write-Host "Downloading $($ssrsItem.Path)$($extension)";
# Create download folder if it doesn't exist (concatenate: "c:\temp\ssrs\" and "/SSRSFolder/")
$downloadFolderSub = $downloadFolder.Trim('\') + $ssrsItem.Path.Replace($ssrsItem.Name,"").Replace("/","\").Trim()
New-Item -ItemType Directory -Path $downloadFolderSub -Force > $null
# Get SSRS file bytes in a variable
$ssrsFile = New-Object System.Xml.XmlDocument
[byte[]] $ssrsDefinition = $null
$ssrsDefinition = $ssrsProxy.GetItemDefinition($ssrsItem.Path)
# Download the actual bytes
[System.IO.MemoryStream] $memoryStream = New-Object System.IO.MemoryStream(@(,$ssrsDefinition))
$ssrsFile.Load($memoryStream)
$fullDataSourceFileName = $downloadFolderSub + "\" + $ssrsItem.Name + $extension;
$ssrsFile.Save($fullDataSourceFileName);
}
这是基于SQL2016/SSRS2016的,但我认为它应该适用于2012。
SELECT 'http://mySQLServerName/reports/api/v1.0/catalogitems(' + cast(itemid as varchar(256))+ ')/Content/$value' AS url
FROM ReportServer.dbo.Catalog
select convert(varchar(max), convert(varbinary(max), content))
from catalog
where content is not null
原始答案是使用2005年的,我在2016年使用过它,所以我想它应该适用于2008年和2012年。
当我使用它时,我也添加了查询路径,这样我就知道哪个报告是哪个了。
注意:在SSMS v18之前,结果网格限制每个元组最多为64KB,而结果文本限制每个元组最多为8,192个字符。如果您的报告定义大于这些限制,则无法获取整个定义。
在SSMS v18中,这些限制已增加到每个元组的2MB,适用于网格报告和文本结果。
我已经尝试了几种排列组合的脚本,但仍然收到“无法创建代理连接”的错误。 这是“应该”起作用的那个:
#------------------------------------------------------
#Prerequisites
#Install-Module -Name ReportingServicesTools
#------------------------------------------------------
#Lets get security on all folders in a single instance
#------------------------------------------------------
#Declare SSRS URI
$sourceRsUri = "http://hqmnbi:80/ReportServer_SQL08/ReportService2010.asmx?wsdl"
#Declare Proxy so we dont need to connect with every command
$proxy = New-RsWebServiceProxy -ReportServerUri $sourceRsUri
#Output ALL Catalog items to file system
Out-RsFolderContent -Proxy $proxy -RsFolder / -Destination 'C:\Users\arobinson\source\Workspaces\EDW\MAIN\SSRS\HQMNBI' -Recurse
throw (New-Object System.Exception("无法建立代理连接...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
我已经尝试过带htttp://和不带的URI,还尝试了包括端口号等方式。但仍然不能让它正常工作。我们有另外两个SSRS实例,我可以毫无问题地对其运行此操作。
#------------------------------------------------------
#Prerequisites
#Install-Module -Name ReportingServicesTools
#------------------------------------------------------
$sourceRsUri = 'https://ssrs.ourcompany.com/reports'
$localRoot = 'C:\Development\MySSRSProject\Server RDL Backup'
# create all the folders first.
$folders = Get-RsRestFolderContent / -Recurse -ReportPortalUri $sourceRsUri | Where-Object Type -eq 'Folder'
foreach($folder in $folders)
{
# create each folder locally
$localFolder = $localRoot + $folder.Path
if (!(Test-Path $localFolder)) {
New-Item -ItemType Directory -Path $localFolder
}
}
# download the reports
$reports = Get-RsRestFolderContent / -Recurse -ReportPortalUri $sourceRsUri | Where-Object Type -eq 'Report'
# clear out modified date file:
Clear-Content ($localRoot + "\lastModified.txt")
foreach($report in $reports)
{
# download each report to appropriate folder
$localFolder = $localRoot + $report.Path
# replace '/' with '\' in path
$localFolder = $localFolder.Replace('/', '\')
#remove report name from path
$localFolder = $localFolder.Substring(0, $localFolder.LastIndexOf("\"))
Out-RsRestCatalogItem $report.Path -ReportPortalUri $sourceRsUri -Destination $localFolder -Overwrite
# write report last modified date and by to a file:
$outString = $report.Path + " last modified by " + $report.ModifiedBy + " on " + $report.ModifiedDate.ToString("yyyy-MM-dd HH:mm:ss")
$outString | Out-File -FilePath ($localRoot + "\lastModified.txt") -Append
}
无法找到类型[Microsoft.ReportingServicesTools.ConnectionHost]
的问题 - 这是在ReportingServicesTools
包中定义的。结果发现根本原因是从网络路径运行脚本。一旦我从C盘运行它,它就正常工作了。 - Viktor$sourceRsUri
去查看/ReportService2012.asmx?wsdl
,因为我正在运行SSRS 2012,但是当运行脚本时,它一直在查看/ReportService2010.asmx?wsdl
,从而导致错误。 - Nii