How Do I Bulk Upgrade SSRS RDLs from SQL Server 2005 to SQL Server 2008 R2?
I discussed how I needed to update each SQL Server Reporting Services (SSRS) RDL in yesterday’s blog post called, “What Does the Error Message, “Unknown Report Version 9.0,” in Visual Studio 2015 Mean?” In order to update the RDLs I would have to perform a, “Save As,” or edit each report. In the closing section of that post, I complained that with over 600 reports, I needed to find a way to bulk upgrade SSRS RDLs from SQL Server 2005 to SQL Server 2008 R2. It took a little time and research, but in the end I stumbled upon a way that may not be completely automatic, but it’s still a great time saver!
This blog post will show you how to bulk upgrade SSRS RDLs.
Let me begin by reminding you about the problem that I had with the, “Unknown Report Version 9.0,” error message. I was upgrading all of my SSRS 2005 solutions and projects to the current SQL Server Data Tools (SSDT) version. It turns out that the current SSDT solution/project can be upgraded, but the RDLs within the solution can’t be upgraded or saved. The error generated was, “Unknown Report Version 9.0.” As I explained in my previous blog post, support ended for SQL Server 2005 in April 2016, so it made sense that the current version of SSDT also wouldn’t support SQL Server 2005.
Based on this error message, I determined that a three-step upgrade process needed to be performed:
-First, upgrade the SQL Server 2005 solutions/projects to another version of SQL Server, such as SQL Server 2008 R2.
-Next, you need to do one of the following:
i). Save each report with a new name, which will also update the SSRS file version, by using, “Save As.” In my particular case, I also needed to rename each report back to the original name!
ii). Edit the report and save it as you normally would.
-Finally, upgrade the solution and project to the latest version of SSDT.
To put it mildly, this process is a huge pain.
Bulk Upgrade SSRS RDLs
I spent some time researching how to perform a bulk upgrade of SSRS RDLs. I even posted a note in the SQL Server SSRS forum. While waiting for someone to answer my question, I decided to try several things on my own. I found one solution that, although not automated, will work without too much effort.
It’s really simple! Open your report from the Solution Explorer window in SQL Server Business Intelligence Development Studio (BIDS) SQL Server 2008 R2.
Without making any changes to the report, the report tab will show that the report already has changes to it. You can see this by the asterisk sign, “*,” at the end of the tab. Once you save the report, the report is upgraded automatically to the SQL Server 2008 R2 schema.
Why am I upgrading to SQL Server 2008 R2 instead of the latest version? First, in my experience with upgrading RDLs, there are generally fewer problems when upgrading to the next version (i.e. 2005 to 2008 R2) instead of upgrading several versions ahead. Second, I already had BIDS 2008 R2 installed in my lab and on my laptop, so let’s say it was more convenient!
Next, in order to save time, select all of the reports within a project. Right-click and then select Open. This will open all of the highlighted reports.
Now that all of the reports are open and you see the asterisk signs (purple arrows), click on the Save All icon to save the reports. This will upgrade all of the RDLs to SQL Server 2008 R2.
Once the reports are saved, the asterisk signs, “*,” disappear from report tab!
Now that all of the solutions and projects are upgraded to SQL Server 2008 R2, you can then move onto the third and last step which is upgrading to the current version of SSDT. This will upgrade all of the solutions/projects and all of the RDLs to SQL Server 2016 or SQL Server 2017. Since there are no tricks or catches with that process, I’m not covering it here. Once the final upgrade is complete, you can edit your solutions, projects and RDLs using the current SSDT.
If you have any questions, please feel free to contact me @GarthMJ.
Do you have an idea for a blog post about a Configuration Manager (SCCM) query or reporting topic? Let me know. Your idea might become the focus of my next blog post!