Copy SSRS reports from Dev to Stage to Prod.

Question

Copy SSRS reports from Dev to Stage to Prod.

Jeff Moden

SSC Guru

Points: 1004340
More actions
October 28, 2014 at 7:58 am

#397570

Pardon my ignorance of SSRS. I've never used it before and need to know how to "promote" reports.
We have a separate Dev, Staging, and Prod server, each with SQL Server and SSRS up an running.
My question is, is there a way to "promote" reports from Dev to Staging to Prod using only "native" tools OR some trickery from the Command Prompt or using PowerShell? I've been looking and haven't found a reasonable solution. We'll need to "promote" possibly "dozens" of reports and all the methods I've seen so far only handles one at a time. Command line or PowerShell is certainly an OK thing for us.
The goal is that I'm trying to avoid having to use products like TFS (which would require more expense) or products like ReportSync, which is cool but is an unsupported .EXE. We're also trying to avoid the expense of using the good code the companies such as Pragmatic Works offer. We're looking for something that we can modify if we need to someday in the future.
Thank you very much for your help on this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I have used this in the past with success.

If you're working on a whole folder at a time, you can set the source folder and the target folder using the command line arguments.

Hope this helps

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Thanks, ol' friend. Great minds think alike. I just found the following example that uses RS and, at first blush, looks fine. Combined with the official link that you provided, I think I just might be able to pull this off in a relatively simple fashion. I might even be able to avoid the VB script. Thank you so very much for the quick response.

http://www.databasejournal.com/features/mssql/article.php/3889376/How-to-Automate-Report-Deployment-Using-SSRS-Web-Service.htm

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

As a bit of a sidebar, I read some of the comments that follow that article and some dude said the performance of RS "sucks". When I read that it took a mere 30 minutes to deploy 300 reports and offered no alternative, my immediate thought was "Dude, you can't be serious".

It's a good article but I do wish the author of that article would have taken the time to respond to some of the posts that indicated some trouble. Guess I'll find out any caveats on my own. 😛

Thanks again, Gianluca. I appreciate the help you've given on this. I'll let you know how it all works out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

I used it a couple of times and it worked flawlessly for me.

I migrated a 2008 to 2012 and it had hundreds of reports. It took a couple of minutes, no more.

I hope it fits your needs.

Actually, deploying reports from dev to prod could also be done in Visual Studio, but I have no first hand experience on that.

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

That's great amplifying information. I'm also glad to see that you can also promote data sources with the understanding that you might need to change some credentials. I'm even happier to see that you have the choice of NOT migrating data sources. That was one of my goals. Using careful naming conventions in the reports themselves, we want to be able to promote the reports using all existing data sources.

This very well could be the key to me creating the automated bullet that I've been looking for. Thank you, again!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jared SSC Guru Points: 61793 More actions · Answer 6

We used RSScripter, by Jasper Smith, and it worked really well for us. Can't find an original link, but Google gives many results and reviews.

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Thanks for the tip. I found the following website on the subject.

http://execsql.org/rs-scripter-tool-handy-tool-to-create-deployment-script-for-reporting-services

One of the steps is to provide a path the RS.exe file so I think it's purely an interface to that program. That's not a bad thing. That will allow people not familiar with the Command Line or VBS to easily get the job done.

Thank you, Jared.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

P Jones SSChampion Points: 12361 More actions · Answer 8

It's so automatic that I had to think hard as to whether I'm mis-reading something in your post, but this is what we do to deploy reports.

We set up project configurations and deploy by right clicking either the individual report or the whole project in visual studio (BIDS)

In solution explorer window:

right click the solution (tip, set tools, options, projects and solutions to have Always show solution ticked) - it's the top item of the lot

Choose configuration manager and set up the three different configs. dev Test and Live in our case.

Then right click the project (below solution) and choose properties and set up your target report server details, datasource folder, overwrite datasources etc for that configuration. Save and repeat for each configuration.

By selecting the correct configuration property for the solution, you can then right click one or more reports (multi-select) to deploy to the correct server with a click.

If I'm copying a load of reports from one server to another I use the rsscripter tool