Tuesday, April 13, 2010

Continuous Installation and MS SQL Reporting Services

A few weeks ago, I was informed that we also will use MS SQL Reporting Services in our programs for the reporting. Now I was used to Crystal Reports, which are easy to deploy because they are compiled into the assembly. But fust for making things more complicated, the company decided to use MS SQL Reporting Services.
From deployment point of view, this is a pain !
The reason : there is no real deployment strategy for seen by MS (as far as I could find out). There are 3 options :
° distribute via the publish in Visual Studio
° use the SOAP interface exposed by MS SQL Reporting Services
° there is an rs.exe, but the usage is just medieval.

Option 1
NO GO, not for 75+ servers !
this requires direct access to all of the servers, can not be sheduled, would be difficult with the current version setup we have, ....

Option 2
This would mean writing a program for communicating with the soap interface, plus its errorhandling, and more important : the data (reports and the like) must still be supplied somehow

Option 3
This seemed the best way to start, and it turned out to be so. The rs.exe is indeed medieval, but there is a nice UI : RSScripter.
RSSCripter makes a batch file that calls rs.exe with all the needed arguments (neat), and exports all wanted items into a folder structure.

So I went for option 3, the approach is as follows :
1) our reporting guy creates the various reports (yes we have a real reporting guru)
2) when he thinks if all are some reports are ok, he uses the RSScripter to export all needed items (reports, datasources, folders, ...)
3) he places this in a dedicated folder in source control, each 'version' is a subfolder of this dedicated folder.
4) he presses 'force build' on the 'make package' project, which just zips the folder

and now I have a package I can deploy :-)

For the deployment itself, there are still some small steps needed though :
° adjust the batch file
a) update the log file location
b) update the scriptlocation
c) update the target reportserver
° adjust some rss files (report and datasource files)
a) update any connection strings
b) update user and password
c) update the source location

when all this is done, I just call the updated batch file.

Nice and easy