从SSRS服务器自动导出所有报告为RDL格式

Question

SSRS提供了将报告导出为原始RDL格式的功能：http://sql-articles.com/articles/general/download-export-rdl-files-from-report-server/。不过，我想知道是否有一种方式可以通过命令行接口或某些工具将所有报告导出为原始RDL格式，并进行压缩等操作。谢谢您的时间。

- Chris K.

Answer 1

我已经创建了一个PowerShell脚本来完成这个任务。您需要连接到具有SSRS数据库的SQL服务器。它将所有文件压缩成一个zip文件。

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

- Alexandru-Codrin Panaite

Answer 2

我没有测试过，但它似乎可以满足你的需求。

https://gallery.technet.microsoft.com/scriptcenter/SSRS-all-RDL-files-from-00488104

- Alan Schofield