SSIS Tip: Managing SQL Server Reporting Services Versions

Once you start working with Microsoft’s reporting solution, you will find there are multiple versions of SSRS, and some of the versions have different specifications for the RDL files which hold your report definitions.

In addition, there are different versions of Report Manager (and the rendering engine) which have different capabilities. And you can also publish RDL’s through SharePoint.

And there are two different tools (Report Builder [RB]and Business Intelligence Design Studio [BIDS]) you can use to build RDL’s to deploy on a Report Manager instance. For those of you on newer versions of SQL Server, the Data Tools [DT] package is the rebranded BIDS.

 This leaves us with a fancy looking matrix:

Note! Remember that if you’re still running SQL Server 2008 or 2008 R2, Microsoft is ending extended support for that platform in 2019. So you need to be working on upgrading those versions now.

Generally speaking, if you know your Report Manager version, you install the proper version of BIDS/DT or RB to match and start building reports. Since your RDL and Tool and Report Manager versions all match, you don’t have to worry about versioning.

Our best practice is to use BIDS or DT to build RDL reports. Report Builder does not let you access all of the possible settings in an RDL, and generally speaking everything you can design in RB, you can also design in BIDS/DT.

Installing the version of the development tool you want can sometimes be difficult, however. Data Tools 2015 and 2017 can be downloaded directly from Microsoft, but the previous versions cannot. Those you have to install from the SQL Server installation DVD, if it is available.

 As a result you may encounter one of these scenarios:

• You can only get ahold of a newer version of BIDS/DT than your Report Manager server.
• You need to move existing reports from one version of SSRS to another without rebuilding them from scratch.
• You inadvertently build reports in one version, then find you can’t publish them on the Report Manager instance you have available.

Wait… what version of Report Manager am I running?
If you can connect to your Report Manager server via Internet Explorer, go to your equivalent of this URL:

And take a look at the targetNamespace node:

 Publishing Reports by Deployment

When you have a version-matching development tool and server – and an Active Directory-based account that can authenticate to Report Manager, you can deploy reports directly from BIDS/DT into Report Manager.

Depending on the version of BIDS/DT that you have, you may need to deploy an entire SSRS project at a time (which argues for making each RDL its own project) or you can right-click on an individual report in Solution Explorer and Deploy selectively. (Assuming you’re not using a buggy version of SSRS, where the entire project deploys regardless.)

For Different Versions
If your Report Manager is on a different, but supported, RDL version than BIDS/DT. You can right click the Project node, choose Properties and then Target Server Version to pick a specific level…

This is the same dialog where you set your Server URL and other info necessary for deployment, so that is handy.

Now when you actually Deploy to that server, the deployment process includes a step where the XML of the RDL is modified to match where you’re deploying. If you have the View > Output window open, you can see what happens during this step.

Note that when you create the RDL, you are creating it in the “native” version level of your copy of BIDS/DT and a different – modified – RDL is written out to the server. Most of the time, that is perfectly fine. However, as you can see from the documentation, you can only publish “down” in versions this way.

Publishing by Hand
Additionally, you might not be logged into a workstation inside the same AD domain (or a trusted one) as the Report Manager server. In fact, you might find yourself having to manually publish the reports one by one.

For this, you still need an AD account that will let you log into the Report Manager server’s web interface. In this scenario, you go to the Report Manager URL and Windows pops up an authentication dialog.

Enter the AD username and password and you’re in Report Manager. If you have the proper permissions, you will then see (in the toolbar) an Upload File button:

Clicking that button will let you pick up a single RDL from the local file system and upload it into the Report Manager database, publishing the report in the folder you are viewing when you click the button.

When Report Manager processes the file this way, it checks the version of the uploaded file. If the RDL version is older than the Report Manager version, in most cases it will silently upgrade the schema and publish the report.

However, sometimes, you’ve uploaded a later version, in which case you get an error like:

You might even get this error when you’ve developed in a higher version, set the Target Version level, and then manually uploaded the RDL.

 Why? Excellent question!

The reason is that BIDS/DT is maintaining two (2) different versions of the RDL. The one you’re editing, and then a second copy in the \debug\bin folder which is the “targeted” version.

To get the “targeted” version:

1. Set the TargetServer version in your project to match the server you’re uploading to.
2. Do all of your editing in BIDS/DT. Save your work.
3. Right-click the report in Solution Explorer and choose Build. This will create a modified copy of your base RDL in \debug\bin
4. Go to the Report Manager web-server, go the folder you want to publish in. Click the Upload File
5. Pick the \debug\bin version of the RDL.
6. Should publish OK!

But what if you’re working with a copy of BIDS/DT that can’t do targeted versions?

Then it’s time to crack out your text editor of choice… and edit the XML inside the RDL itself.

Downgrading from 2016 to 2010

1. Find the xmlns property and change it from:

to

2. Find the <ReportParametersLayout> node in the XML and delete the entire node, including it’s contents.
3. Save the RDL file from the text editor.
4. Upload to the reporting server to test!

Downgrading from 2010 to 2008

1. Find the <Report> node and change it from:

to 

2. Find the <ReportSections><ReportSection> starting elements in the XML and delete just those two starting tags – but NOT the contents.
3. Find the ending tags </ReportSection></ReportSections> and delete those as well. Just keep everything between them.
4. Look for and remove these nodes, including their contents:

5. Save the RDL file from the text editor.
6. Upload to the reporting server to test!

Downgrading from 2008 to 2005
Now you’re stuck. The RDL formats between 2008 and 2005 are too different to tweak manually – at least, not without a lot of trial and error.

Downgrading from 2005 to 2000
But these two versions are close enough to down-convert:

1. Find the xmlns property and change it from:

to

2. Find all of the < InteractiveWidth> node(s) in the XML and delete the entire node, including its contents.
3. Find all of the <InteractiveHeight> node(s) in the XML and delete the entire node, including its contents.
4. Save the RDL file from the text editor.
5. Upload to the reporting server to test!

Upgrading from Low to High Version
Interestingly, you can also bump up from 2008 to 2010 to 2016 by updating the namespace definition in the lower-version file. Note that you will need to test the updated report thoroughly, and you will likely have to reconnect the Data Source.

If you want to get fancy, you can add in the missing nodes by hand. But if you don’t BIDS/DT will add them when the report is opened in the designer… they will just be empty.

Do this in the text editor, then open the RDL in the higher version tool. The newly added node will be filled in by BIDS/DT but may not be completely working until you set any related properties.

Extra Credit
If you have a lot of time on your hands, and want to learn a cool new technology, you can create an XMLT (XML Transform Definition) to convert between each format and the other(s).

And finally…
As ever, if you need help with Meditech DR index creation, reporting, extract or analysis please feel free to reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!