Can PowerShell script out SQL Server Reporting Services RDL files?

Question

Is it possible to use PowerShell to script out SQL Server Reporting Services rdl files in SQL Server 2008? If so, can someone provide a code example of doing this? This would be a useful replacement for using a 3rd party tool to script out RDL files created by business users outside of my Business Intelligence department.

CLARIFICATION OF THE TERM "SCRIPT OUT"

By "script out", I mean I would like to automatically generate the underlying RDL file for each report on the server. For instance, when you code report in BIDS, you are generating a RDL file. When you deploy the file to the server, the file is somehow imported into the SQL Server ReportServer database and it is no longer a separate physical RDL file. I would like to extract all the reports from the server in a RDL file format.

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

CLARIFICATION ON WHY I WANT TO GENERATE RDL VERSIONS OF REPORTS

Why is it important to "script out" the reports to RDL files? I would like to check-in the RDL files to my source control system once a night to keep track of all reports created by users outside of my Business Intelligence department. I already keep track of all reports generated by my department since we develop our reports in BIDS, but I can't keep track of versioning history on reports built in the online Report Builder tool.

CLARIFICATION ON WHY POWERSHELL AND NOT SOMETHING ELSE

1. 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 a 3rd method.

2. Opportunity to expand my problem solving toolbet via PowerShell. Using PowerShell to solve this problem may provide the foundation for learning how to use PowerShell to solve other problems that I haven't tried to solve yet.

3. PowerShell is easier to understand for my team and me. In general, my team members and I can understand PowerShell code more easily than .NET code. Although I know this problem can be solved with some .NET code using the API (that's how RSScripter works after all), I feel it will be easier for us to code and maintain a PowerShell script. I also realize a PowerShell script will probably use .NET code, but I'm hoping PowerShell will already be able to treat the reports like objects in some way so I won't have to use the Reporting Services API to extract the files.

4. RSScripter doesn't support 2008 yet. In the past, I've used RSScript to script out reports. Unfortunately, it doesn't appear to support 2008 yet. This means I have to write code against the API right now since that's the only way I present know how to extract the files in an automated unattended manner.

Answer 1

Anything that supports .Net can do this. See this Stackoverflow posting for some links to the API docs. The process is actually fairly straightforward - the API has a call to upload or download the .rdl file.

Report models are a bit more fiddly. You have to get the dependent reports (again an API call) and re-connect the data source if you upload a new report model. Again, not terribly strenuous.

Powershell should do this fine. I've variously done this with IronPython and C#. There's a also a tool called rs.exe that takes a vb.net script, tops and tails it with some includes and compiles and runs it behind the scenes.

Answer 2

PowerShell doesn't provide any native, PowerShell-esque functionality for this, no. You can do this in PowerShell (as noted in the previous answer) only because PowerShell can access the underlying Framework classes. As you noted in your comment to the previous answer, it's no different from using the API in C# or VB.

The SQL Server team has not yet provided much in the way of PowerShell-specific stuff. They're primarily relying on .NET and T-SQL as "scripting languages."

Answer 3

I just realized the Content column in the ReportServer.dbo.Catalog contains the definition in an 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.