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
-
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.
-
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.
-
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.
-
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.
Best Answer
a little late, but here you go
This PowerShell script : 1. Connects to your report server 2. Creates the same folder structure you have in your Report Server 3. Download all the SSRS Report Definition (RDL) files into their respective folders
https://sqlbelle.wordpress.com/2011/03/28/how-to-download-all-your-ssrs-report-definitions-rdl-files-using-powershell/