Can a PowerShell script output SQL Server Reporting Services RDL files?

Question

Can I use PowerShell for a script from the rdl files of SQL Server Reporting Services in SQL Server 2008? If so, can anyone provide some sample code for this? This would be a useful replacement for using a third-party script tool from RDL files created by business users outside my Business Intelligence department.

CONFIRMATION OF THE SCRIPT OUT TIME

From "SCRIPT out", I mean that I would like to automatically generate a basic RDL file for each report on the server. For example, when you submit code to BIDS, you create an RDL file. When you deploy the file to the server, the file is somehow imported into the SQL Server ReportServer database and is no longer a separate physical RDL file. I would like to extract all reports from the server in RDL format.

I used the RSScripter tool to extract reports as RDL files, so I know that this is possible using tools other than PowerShell. I would like to know if it is possible to do this using PowerShell, and if so, get a sample code to do this.

ACKNOWLEDGE WHY I WANT TO GENERATE RDL REPORT VERSIONS

Why is it important to "SCRIPT output" reports to RDL files? I would like to register RDL files in my version control system once a day in order to track all reports created by users outside my Business Intelligence department. I already track all reports created by my department, since we develop our reports in BIDS, but I can’t track version history in reports created in the online reporting tool.

STATEMENT WHY AUTHORITY AND NOT SOMETHING ANYTHING

• Curiosity

  . I have a problem that I know can be solved by one of two methods (API or RSSCripter), and I would like to know if it can be solved by the third method.

• Ability to expand the set of tools for solving problems using PowerShell. Using PowerShell to solve this problem can be the basis for learning how to use PowerShell to solve other problems that I have not yet tried to solve.

• PowerShell is easier to understand for my team and me. In general, my team members and I can better understand PowerShell code than .NET code. Although I know that this problem can be solved with some .NET code using the API (which is how RSScripter works), I feel that it will be easier for us to code and maintain a PowerShell script. I also understand that the PowerShell script will probably use .NET code, but I hope that PowerShell will already be able to process reports as objects in some way, so I don’t have to use the Reporting Services API to extract the files.

• RSScripter does not yet support 2008. I used to use RSScript for script reports. Unfortunately, it does not yet support 2008. This means that I have to write code against the API right now, as the only way I present is how to extract files in automatic automatic mode.

Answer 1

bit late but here you go

This PowerShell script: 1. Connects to your report server. 2. Creates the same folder structure as on your report server. 3. Download all SSRS Report Definition (RDL) files to the appropriate folders

http://www.sqlmusings.com/2011/03/28/how-to-download-all-your-ssrs-report-definitions-rdl-files-using-powershell/

Answer 2

PowerShell does not provide any useful PowerShell-esque functions for this, no. You can do this in PowerShell (as indicated in the previous answer) only because PowerShell can access the base classes of the Framework. As you noted in your comment on the previous answer, this is no different from using an API in C # or VB.

The SQL Server team has not yet provided many features for working with PowerShell. They mainly rely on .NET and T-SQL as scripting languages.

Answer 3

I just figured out the content column in ReportServer.dbo.Catalog contains the definition in image format. I wrote the following code to convert it to readable text:

SELECT CONVERT(VARCHAR(MAX), CONVERT(NVARCHAR(MAX), CONVERT(XML, CONVERT(VARBINARY(MAX), Content)))) FROM [ReportServer].[dbo].[Catalog] WHERE Type = 2 

With the above code, I can now automate writing the results to a flat file, and then import the file into my version control system.

Answer 4

Anything that supports .Net can do this. See fooobar.com/questions/851794 / ... for some links to API documents. The process is actually quite simple - the API has a call to load or download the .rdl file.

Report models are a bit more complicated. You should receive dependent reports (again an API call) and reconnect the data source if you upload a new report model. Again, not very stressful.

Powershell should do it well. I did it differently with IronPython and C #. There's also a tool called rs.exe that accepts a vb.net script, peaks and tails, some of which include and compile and run it behind the scenes.