SSRS: Migrating Reports from One SharePoint Server To Another

Sounds easy? Problem free? Think again ...

This blog post is a step-by-step guide to copying a Report (consisting of a Report, one (or more) Shared Datasets, and a Data Source) from an existing Microsoft SharePoint 2007 document library into another document library on a different server (such as you might want to do when taking a production report into development for enhancement).

You can probably thing of at least three ways to do it off the top of your head;

1. Save each item into a file on your local disk and then upload them to the new server using the standard "Upload" button on the document library

2. Highlight each item in turn and under the menu choose "Send to" and then "Other Location" and specify the location of the document library on the new server

3. As 1, except "hack" the report file so that it works.

Way 3 works so you might want to jump straight to that bit if you're in a rush. I'll go through 1) and 2) and let you know why they don't work and explain some of the errors you'll encountered if you try them (and what probably got you to google for a solution anyway!)

0. Just Create the Data Source

It's much easier than trying to copy it - just create a new one.

1. Save and Upload

Locate your dataset on the existing server;

The dataset I'm going to be working with is called "Parameter Default Values" and is highlighted in the image above.

Bring up the menu for the affected dataset and then choose "Send To" and then "Download a Copy". Then you should be prompted to save the downloaded file to local storage.

Next locate the dataset library on your new server;

Choose the "Upload" button (as highlighted above) and then "Upload Document";

Select the file you just saved the data set to and click "OK".

Click "OK".

The report file has now loaded, apparently correctly, into the Document Library. Unfortunately it's not actually worked and is in an unusable state. To show this highlight the new data set and choose "Manage Datasource" from the popup menu;

The text of the error is;

The item XXX cannot be found ---> Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException: The Item XXX cannot be found

You'll notice that the URL's for the error message feature the OLD server. The file has been migrated with hard-coded links and unfortunately the new server does not allow you to update these links so what you're left with is a problem that you can't fix (well, within SharePoint anyway).

You have to question whether the "Upload" button should actually be enabled for importing SSRS items as it doesn't work.

2. Send To > Other Location

Locate the dataset on the original server;

Hover over the data set and choose "Send To" and then "Other Location";

Enter the URL for the new document library (click to test).

Click "OK"

And this is as far as it gets ...

And now you need to try and stop it "copying". The only way I've found to do this is to kill the process in Task Manager. 

It's possible that there could be some strange permissions problem but, to be frank, I've clicked "Test" and that worked so why wouldn't the copy??!! Very poor process design Microsoft ...

3. Export/ Edit/ Upload

And here is the "working" method for migrating items between two servers.

Migrating Data Sets

Locate the data set on the original server;

Bring up the menu for the affected dataset and then choose "Send To" and then "Download a Copy". Then you should be prompted to save the downloaded file to local storage.

Open the locally downloaded file using a text editor (I prefer Notepad++ but practically anything will do);

There are TWO places in the downloaded data set where the URL of the Datasource and the Reporting Server are stored. Thankfully they are located at the top and bottom of the file so don't require much searching!

Locate the two entries (search for DataSourceReference, and ReportServerUrl) and update the URL's to point to the new server. Save the edited file.

Next locate the dataset library on your new server;

Choose the "Upload" button (as highlighted above) and then "Upload Document";

Select the file you just edited and saved and click "OK".

Click "OK" (we don't want to change anything).

The report file has now loaded, apparently correctly, into the Document Library. To verify this select the data set and choose "Manage Data Sources";

Seems to be working (well if it's not you can at least change it!).

Migrating Reports

Ironically this works in exactly the same way as migrating a report from a non-SharePoint server (that I have documented here).