Saturday, 16 August 2008

Report Loader for SQL Server Reporting Services

I recently had to deploy some reports to SQL Server Reporting Services and found that Microsoft don't supply any tools to make this easy. My requirement was to be able to take reports from developers and deploy them directly to Reporting Services and fix up the database connections to point at the correct SQL server for the environment. With the help of the code from Jason Smith at SQLdbatips.com I wrote a command line deployment program in C#.

The command line syntax for this program is shown below:

ReportLoader [--help|-?|-h] (--url=targeturl|--server=targetserver)
[--source=sourcefolder] --target=targetfolder [--connstring=connstring]
[--username=username --password=password]

--help - this information
--url - target rss server url
--server - target server name, defaults to current folder
--source - source folder containing reports (*.rdl) and datasources (*.rds)
--target - target folder name
--connstring - connection string to use
--username - username to use in datasource
--password - password for username
--delete - delete all reports before upload

Examples:
ReportLoader --url="http://localhost/ReportServer/ReportService2005.asmx" --source="D:\My Reports\Reports" --target="/My Reports"
ReportLoader --server=rsserver --source="D:\My Reports\Reports"
--target="/My Reports" --connstring="Data Source=SQLSERVER;Initial Catalog=Database"
--username=DOMAIN\SvcMyReports --password=pass


The command line help doesn't say it but this will also upload GIF and JPG files it finds in the source directory.

If you would like to use this the source and compiled application are available here: ReportLoader.zip. If you have any comments about it or suggestions to improve it please let me know.

If I get time I would like to convert this application in to a custom action for Windows Installer that can be used from WiX.

Edit: 05 October 2008 - Updated download to include -url parameter.

11 comments:

Murray said...

Hi Neil,

I had a quick play with your loader program. I tried using the URL parameter and it said it didn't exist. Looking at the source code i saw it was missing from the parameter list so i added it in as url=? which helped, but i received a few other SOAP exceptions after that.

Just thought id let you know about the URL parameter.

Neil Sleightholm said...

Thanks for info, I have updated the code to include the missing parameter.

Christopher Painter said...

Hey Neil-

I was looking at this for a project at work but I don't see this web service in SQL 2008. Does this code work on SQL 2005 only?

Neil Sleightholm said...

I am afraid I haven't tried it on SQL 2008 yet.

Christopher Painter said...

I'm finding references in the SQL2008 books online to a ReportService2005 namespace in the ReportService2005.dll assembly. It seems to be a client wrapper for the webservice but I can't find the DLL anywhere. I can find MSDN topics on it.

Did you have any luck with this? I'm guessing that we shouldn't have to consume the web service directly.

Neil Sleightholm said...

I am installing SQL2008 with Reporting Services now to inverstigate what has changed. It has been a while since I looked at this code but from what I remember it talks to the a web service to perform all the actions. Have you had a look at the sqldbatips site, I think Jason has updated his code to work with RS2008.

Christopher Painter said...

Thanks for the tip. This is my first day looking at this problem domain so I'm really at the ground floor right now.

I was kind of hoping there was a WiX extension that I could throw into a fragment and build as a merge module to insert into my currently InstallShield/WiX hybrid product line installer.

Good thing I have my new MSDN subscription... I see myself working on this at nights now when there are no developers around bugging me to get other things done first.

Neil Sleightholm said...

I have had a quick play and it seems to work but you have to use the --url syntax not --server. The url will look like this: http://localhost/ReportServer_SQL2008/ReportService2005.asmx, replace ReportServer_SQL2008 with the value you have set in the "Reporting Services Configuration Manager".

The other thing I have noticed it that this program uploads *.rdl and *.rds files, in VS2008 the extension for reports has changed to .rdlc and I am not sure what the datasource extension is.

Anonymous said...

this loader works like charm and saves me a lot of time in loading report files onto report server 2005. Many thanks.

Mattias said...

Hi Niel
You didnt by any chance convert this tool to a cuastom action for Wix ?

Neil Sleightholm said...

No I am afraid I never did.