是否可以在 Visual Studio 中导入现有的 SSRS 报告？

Question

升级计算机时，我们丢失了用于创建 SSRS 报告的 Visual Studio 项目。然而，数据源和报告仍然存在于服务器上。有没有办法使用 SQL Server 上的内容重新创建 VS 项目？有没有办法创建一个新的 Reporting Services 项目并在其中导入现有的数据源和报告？

我相信这些报告最初是使用 VS 2005 创建的。

Answer 1

你并没有失去太多。

数据源并不多：数据库的连接字符串，以及可能的缓存和身份验证设置。这些应该很容易重新创建。

可以下载每种报表类型的报表定义（.rdl 文件），并将其添加到新的 Reporting Services 项目中。它们需要指向新重新创建的数据源，但这样应该就可以了。

要下载报告文件，请访问 Reporting Services 报告管理器（网站）。对于具有默认安装选项的 SQL 默认实例，此值为 http://servername/reports/ 如果您具有管理员权限，您可以在那里浏览报告。转至给定报告的属性并单击编辑...按钮。这将通过您的浏览器下载 .rdl。 （在 SSRS 2008 中，“编辑”按钮更改为“下载...”）

您需要了解您正在运行的 SSRS 版本：Business Intelligence Developer Studio 的不同版本（BIDS、Visual 的 SSAS 和 SSRS 版本） Studio）为特定版本的 SSRS 创建报告。报告可以升级，但不能降级或部署到旧版本的 SSRS。

Answer 2

现在有一个 PowerShell 模块可以很好地完成这项工作。

out-rsfoldercontent -reportserveruri http://[reportserver name]/reportserver -RsFolder /[您要导出的路径] -Destination c:\test -recurse

调整参数以适应。

https://github.com/Microsoft/ReportingServicesTools

Answer 3

SSRS 不允许您一次性从报告文件夹中下载所有报告...

但是我发现并调整了我在网上找到的一段简单的 SQL，我们使用它对我们的系统产生了巨大的影响...

就是这样:-

/*
People working on SSRS are well aware that “Report Manager” does not support downloading all the report files (.rdl files) at one go out-of-box.
However take this script, alter the xxx parameters to your bits. Its works great.
If you get a HOST Error - you need to set full permission to the SQL Server Group on your DOS Dir. 
on [Our_Prod_Server], this is: SQLServerMSSQLUser$NS226758$MSSQLSERVER

NOTE: You will find a RETURN; statement below, comment it out once you have altered the parameters.
*/

--Replace NULL with keywords of the ReportManager's Report Path,  
--if reports from any specific path are to be downloaded 
--select * from [ReportServer].[dbo].[Catalog] CL -- this gives you an idea of the Report Directories.
DECLARE @FilterReportPath AS VARCHAR(500) = 'xxx'  

--Replace NULL with the keyword matching the Report File Name, 
--if any specific reports are to be downloaded 
DECLARE @FilterReportName AS VARCHAR(500) = '' 

--Replace this path with the Server Location where you want the 
--reports to be downloaded.. 
DECLARE @OutputPath AS VARCHAR(500) = 'C:\Users\[uuuuu]\Documents\Visual Studio 2012\Projects\Report Skeleton\[Report DIR Name]\' 

--Used to prepare the dynamic query 
DECLARE @TSQL AS NVARCHAR(MAX) 

RETURN;

--Reset the OutputPath separator. 
SET @OutputPath = REPLACE(@OutputPath,'\','/') 

--Simple validation of OutputPath; this can be changed as per ones need. 
IF LTRIM(RTRIM(ISNULL(@OutputPath,''))) = '' 
BEGIN 
  SELECT 'Invalid Output Path' 
END 
ELSE 
BEGIN 
   --Prepare the query for download. 
   /* 
   Please note the following points - 
   1. The BCP command could be modified as per ones need. E.g. Providing UserName/Password, etc. 
   2. Please update the SSRS Report Database name. Currently, it is set to default - [ReportServer] 
   3. The BCP does not create missing Directories. So, additional logic could be implemented to handle that. 
   4. SSRS stores the XML items (Report RDL and Data Source definitions) using the UTF-8 encoding.  
      It just so happens that UTF-8 Unicode strings do not NEED to have a BOM and in fact ideally would not have one.  
      However, you will see some report items in your SSRS that begin with a specific sequence of bytes (0xEFBBBF).  
      That sequence is the UTF-8 Byte Order Mark. It’s character representation is the following three characters, “”.  
      While it is supported, it can cause problems with the conversion to XML, so it is removed. 
   */ 
   SET @TSQL = STUFF((SELECT 
                      ';EXEC master..xp_cmdshell ''bcp " ' + 
                      ' SELECT ' + 
                      ' CONVERT(VARCHAR(MAX), ' + 
                      '       CASE ' + 
                      '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+ 
                      '         ELSE C.Content '+ 
                      '       END) ' + 
                      ' FROM ' + 
                      ' [ReportServer].[dbo].[Catalog] CL ' + 
                      ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' + 
                      ' WHERE ' + 
                      ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x''' 
                    FROM 
                      [ReportServer].[dbo].[Catalog] CL 
                    WHERE 
                      CL.[Type] = 2 --Report 
                      AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/') 
                      AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name) 
                    FOR XML PATH('')), 1,1,'') 

  --SELECT @TSQL 

  --Execute the Dynamic Query 
  EXEC SP_EXECUTESQL @TSQL 
END

Answer 4

我使用了这段代码，但是我在 SSRS 2008 R2 中遇到了波兰语字符的问题。
所以我添加了一些替换，替换这个“损坏的”转换：

    SET @TSQL = STUFF((SELECT
                  ';EXEC master..xp_cmdshell ''bcp " ' +
                  ' SELECT ' +
                  ' REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE' +
                  ' (REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(MAX), ' +
                  '       CASE ' +
                  '         WHEN LEFT(C.Content,3) = 0xEFBBBF THEN STUFF(C.Content,1,3,'''''''') '+
                  '         ELSE C.Content '+
                  '       END), ''''Å'''', ''''l''''), ''''Ä™'''', ''''e''''), ''''l›'''', ''''s'''') '+
                  '     , ''''ć'''', ''''c''''), ''''l¼'''', ''''z''''), ''''lš'''', ''''s'''') ' +
                  '     , ''''ó'''', ''''o''''), ''''Ä…'''', ''''a''''), ''''l»'''', ''''Z'''') '+
                  '     , ''''sÄ'''', ''''S''''), ''''†'''', ''''C''''), ''''l‚'''', ''''l'''') ' +
                  '     , ''''Ó'''', ''''O''''), ''''Ę'''', ''''E''''), ''''lº'''', ''''z'''') ' +
                  '     , ''''Ä„'''', ''''A''''), ''''l¹'''', ''''Z'''') '+
                  ' FROM ' +
                  ' [ReportServer].[dbo].[Catalog] CL ' +
                  ' CROSS APPLY (SELECT CONVERT(VARBINARY(MAX),CL.Content) Content) C ' +
                  ' WHERE ' +
                  ' CL.ItemID = ''''' + CONVERT(VARCHAR(MAX), CL.ItemID) + ''''' " queryout "' + @OutputPath + '' + CL.Name + '.rdl" ' + '-T -c -x'''
                FROM
                  [ReportServer].[dbo].[Catalog] CL
                WHERE
                  CL.[Type] = 2 --Report
                  AND '/' + CL.[Path] + '/' LIKE COALESCE('%/%' + @FilterReportPath + '%/%', '/' + CL.[Path] + '/')
                  AND CL.Name LIKE COALESCE('%' + @FilterReportName + '%', CL.Name)
                FOR XML PATH('')), 1,1,'')